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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

huohaodian@gmail.com - 06 Feb 2008 18:41 GMT

Hi,

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

Thanks in advance.

Reply to this Message

Jon Skeet [C# MVP] - 06 Feb 2008 18:47 GMT

> 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.

Signature

Jon Skeet - <skeet@pobox.com>
http://www.pobox.com/~skeet Blog: http://www.msmvps.com/jon.skeet
World class .NET training in the UK: http://iterativetraining.co.uk

Reply to this Message

Ignacio Machin ( .NET/ C# MVP ) - 06 Feb 2008 18:56 GMT

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() );

Signature

Ignacio Machin
http://www.laceupsolutions.com
Mobile & warehouse Solutions.

> Hi,
>
> How can I convert a value created from DateTime.Now() to the datetime
> format that SQL Server recognises?
>
> Thanks in advance.

Reply to this Message

Marc Gravell - 06 Feb 2008 19:26 GMT

> //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

Reply to this Message

Nicholas Paldino [.NET/C# MVP] - 06 Feb 2008 19:33 GMT

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.

Signature

- Nicholas Paldino [.NET/C# MVP]
- mvp@spam.guard.caspershouse.com

> Hi,
>
[quoted text clipped - 13 lines]
>>
>> Thanks in advance.

Reply to this Message

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

Free Magazines