• 0

MSSQL 2005 - Difficult date conversion


Question

Hi Guys,

Hoping someone can help me.

Due to a software package we use's way of managing it's database, it seems to use a nvarchar(50) field for it's date... unsure why, but anyway.

In one of the fields, it stores two different formats for dates being:

13 January 2010 09:15:37

and also:

Wednesday, 13 January 2010 11:48:44pm

When i attempt a convert or a cast, i get an errors such as the the following:

Msg 295, Level 16, State 3, Line 1

Conversion failed when converting character string to smalldatetime data type.

Now obviously this is because it doesn't like the two different date formats.. does anyone have any idea how this can be done?? as the original software cannot be changed, however there must be a way to convert or cast?

Link to comment
Share on other sites

2 answers to this question

Recommended Posts

  • 0

You're probably going to need to modify the values being converted into a format that the CONVERT function will understand.

First step would be to strip out the day name component, its redundant. After that, try getting it into a format such like:

mon dd yyyy hh:mm:ss:fffAM

E.g. Jan 13 2010 09:15:37:000AM

You should then be able to successfully CONVERT it:

CONVERT(DATETIME, 'Jan 13 2010 09:15:37:000AM', 9)

The last parameter denotes which format it should be expecting. Check out (http://www.blackwasp.co.uk/SQLDateTimeFormats.aspx) for more info.

Link to comment
Share on other sites

This topic is now closed to further replies.
  • Recently Browsing   0 members

    • No registered users viewing this page.