how to convert char value to datetime

Question

question

suraj asked May 22, '10

i have varchar col in the table which store the date in the format '22-Jul-2009 12:35:29.998'

when i use convert(datetime,qdate) or cast(calldate AS smalldatetime) in sql statement

it gives an error Syntax error converting datetime from character string.

t-sql date-functions data-conversion

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2010-05-22T04:07:48Z

sp_lock answered May 22, '10

The syntax below is correct. What version of MSSQL are you using?

DECLARE @qdate VARCHAR(25)
SET  @qdate = '22-Jul-2009 12:35:29.998'
SELECT cast (@qdate AS SMALLDATETIME )

as does

DECLARE @qdate VARCHAR(25)
SET @qdate = '22-Jul-2009 12:35:29.998'
SELECT convert (SMALLDATETIME, @qdate )

5

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Fatherjack ♦♦ commented · May 22, 2010 at 04:31 AM

+1 - congrats on 3k Jonlee :D

0 ·

Matt Whitfield ♦♦ commented · May 22, 2010 at 04:38 AM

Congrats sir :)

0 ·

sp_lock commented · May 22, 2010 at 04:56 AM

Cheers chaps... Any of you going to the Event on the 17th in London? http://sqlblogcasts.com/blogs/simons/archive/2010/05/14/paul-and-kimberly-are-coming-the-uk.aspx

0 ·

Matt Whitfield ♦♦ commented · May 22, 2010 at 04:42 PM

@Jonlee - no - I'm really busy at the moment - it's sucking quite a lot!

0 ·

David Wimbush commented · May 24, 2010 at 05:16 AM

Hi Jonlee, I'm going. See you there?

0 ·

Answer 2 · 2010-05-22T04:30:59Z

Fatherjack answered May 22, '10

I think you will only get answers that are the same as the answers to your other (very similar) question here http://ask.sqlservercentral.com/questions/6636/what-is-the-best-method-to-use-isdate-in-sql

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.