Convert DateTime.Now() to SQL Server(tm) standard internal format fordatetime

[huohaodian]

Hi,

How can I convert a value created from DateTime.Now() to the datetime
format that SQL Server recognises?


Thanks in advance.

 

[Jon Skeet [C# MVP]]

How can I convert a value created from DateTime.Now() to the datetime
format that SQL Server recognises?

My guess is that you're trying to include it directly in a SQL
statement. Don't; instead use command parameters instead of converting
it into text.

 

[Ignacio Machin \( .NET/ C# MVP \)]

Hi,

Just like that

//If you are using parameters THE PREFERRED WAY
com.Parameters.Add("@adate", SqlDbType.DateTime).Value = DateTime.Now;

//building the text
com.CommandText = String.Format("update T set aDate = '{0}' ",
DateTime.Now.ToShortDateString() );

 

[Marc Gravell]

//building the text

com.CommandText = String.Format("update T set aDate = '{0}' ",
DateTime.Now.ToShortDateString() );

I know it was only presented as the second option, but just to note
that even in this case you are probably at risk if the client has a
different locale to the server; which is yet another reason to use
parameters (aimed at the OP).

Format aside, you may also (depending on the system) have to worry
about timezones ;-p
UTC is never a bad choice in such scenarios...

Marc

 

[Nicholas Paldino [.NET/C# MVP]]

It's not guaranteed that the current thread culture is the one that the
server will use for processing.

Rather, if you MUST use a literal in a constructed string, you would use
the format:

yyyy-MM-dd HH:mm:ss.fff

This will represent down to milliseconds (which is the highest
resolution of the datetime data type on SQL Server). You can choose to omit
anything from one point in the string on (for example, everything from hour
(HH) on), depending on the comparison being made.