CODEWITHSUNDEEP
sqlstringt-sqlsql-server-2005datetime
ADM
ADM

Reputation: 1610

TSQL: datetime from character string error

There are some posts related to this but I'm so new to TSQL that I cannot make sense of them so please excuse me.

My procedure has:

    BEGIN TRY

        INSERT INTO  dbo.myprocedure(
         Mydate

     )
     VALUES(
     CONVERT(DATETIME, @mydate, 102)
)

If I execute it this way: 

EXEC mydbo.dbo.myprocedure @mydate = 'May 8 2012 9:21AM'

I get this error: Conversion failed when converting datetime from character string.

How can I solve this?

Thanks a million

Upvotes: 0

Views: 1777

Answers (3)

Andomar
Andomar

Reputation: 238296

If you look at the documentation for CONVERT, you'll see that format 102 is the ANSI format, or yyyy.mm.dd. So passing a date like 2012.05.08 should work.

It looks like 2012.05.08 9:41 and 2012.05.08 9:41AM will work too, but that's outside the documented format.

Upvotes: 1

fankt
fankt

Reputation: 1047

Change CONVERT(DATETIME, @mydate, 102) to CONVERT(DATETIME, @mydate, 100)

or just CONVERT(DATETIME, @mydate)

reference to CAST and CONVERT and http://www.sqlusa.com/bestpractices/datetimeconversion/ on MSDN

Upvotes: 0

Arion
Arion

Reputation: 31249

Maybe something like this:

EXEC mydbo.dbo.myprocedure @mydate = '2012/05/08 09:21:00'

Upvotes: 0

Related Questions