6 replies to this topic

[DoktorD1313]

In SQL, I need to convert a column of characters (actually dates represented as MM/DD/YY) that the Import/Export Wizard will not recognize as dates.

Since all records now and from here on out will be post 2000, I assumed I would be able to insert a "20" into the current record and then be able to convert it to a date.

For example:

"03/15/11" could be split into "03/15/" + "20" + "11" and recombined to get 03/15/2011 (which is a readable date).

My attempted code was:

INSERT INTO TRIAL (SOLD_DATE)

SELECT CONVERT(VARCHAR(10),(SUBSTRING(FIRST_SOLD,1,6)+'20'+SUBSTRING(FIRST_SOLD,7,2)),1)

FROM LABELS

However, it returns this error:

Msg 241, Level 16, State 1, Line 4
Conversion failed when converting date and/or time from character string.


Any suggestions?

[WingedPanther]

Which database type is this? MySQL is quite different from Oracle, for example.

[DoktorD1313]

I didn't even give that a thought..

I'm using MS SQL Server

[WingedPanther]

What does this return?

SELECT CONVERT(VARCHAR(10),(SUBSTRING(FIRST_SOLD,1,6)+'20'+SUBSTRING(FIRST_SOLD,7,2)),1) FROM LABELS

[DoktorD1313]

When I use that code, I get a column of the appended dates (07/21/2011). So I'm not sure why I'm having trouble to get it to convert to dates.

[WingedPanther]

What's the regional date/time setting on the database server? I've found that 2011-07-21 is the only safe date format you can use.

[DoktorD1313]

Well, for whatever reason all I needed to do was restart the application and repasting the Query worked just fine.

Thanks for the help, though!