String conversion to DATETIME in SQL Server

Question

I need to convert a string to datetime. I need to store datetime with milliseconds in SQL Server 2005.

example:

SELECT CAST('2010-07-28 20:07:25.733000000' AS DATETIME)

when I try I am getting error like

Conversion failed when converting datetime from character string

In addition to the current answers, it's worth noting that if you are going to continue to convert to datetime rather than datetime2, you ought to replace the space between the date and time with the letter T. E.g. '2010-07-28T20:07:25.733' - otherwise, the conversion may fail if different language settings are in force. — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Mar 15, 2012 at 14:17

Aaron Bertrand · Accepted Answer · 2012-03-15 14:11:47Z

3

You'll need to truncate.

SELECT CONVERT(DATETIME, CONVERT(CHAR(23), '2010-07-28 20:07:25.733000000'));

answered Mar 15, 2012 at 14:11

Aaron Bertrand

282k37 gold badges469 silver badges468 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Bhaskara varma Dandu Over a year ago

but i need store complete value.i mean '2010-07-28 20:07:25.733000000'

Aaron Bertrand Over a year ago

...and as @Damien_The_Unbeliever points out, you'll want to be sure that your language and dateformat settings are appropriate, or just pass in yyyy-mm-ddThh:mm:ss.mmm in the first place.

Aaron Bertrand Over a year ago

Then you are using the wrong data type. If you are using SQL Server 2008, you should use datetime2. If not, then there is no way to store that in SQL Server 2005 or lower using a native datetime type. You could store it as a string but I don't recommend that. If these are always trailing zeros, why do you need to store them? You can always add them at presentation time.

Damien_The_Unbeliever Over a year ago

@BhaskaravarmaDandu - A datetime in SQL Server 2005 can only store 3 digits of millisecond information (and the final digit is further restricted), so what you're asking for cannot be accomplished.

Alex K. · Accepted Answer · 2012-03-15 14:12:09Z

2

(With rounding) the milisecond range is 0-999 in a DATETIME, for more precision use DATETIME2 if your using SQL2K8.

answered Mar 15, 2012 at 14:12

Alex K.

177k32 gold badges276 silver badges299 bronze badges

1 Comment

Alex K. Over a year ago

truncate to ensure you don't provide > 3 decimal places as in Aaron's answer, or do you mean store more millisecond precision?

Arion · Accepted Answer · 2012-03-15 14:13:43Z

1

If you remove the last 0:s in the miliseconds this will work:

SELECT CAST('2010-07-28 20:07:25:733' AS DATETIME)

answered Mar 15, 2012 at 14:13

Arion

31.3k10 gold badges74 silver badges89 bronze badges

Collectives™ on Stack Overflow

String conversion to DATETIME in SQL Server

3 Answers 3

4 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related