CODEWITHSUNDEEP
sql-serversql-server-2008date
user183121
user183121

Reputation: 13

Convert varchar dd-mm-yyyy to yyyy-mm-dd date field in SQL Server 2008

hopefully the title describes what I'm trying to do.

I have a varchar field in a SQL Server 2008 table that contains text dates in the format dd-mm-yyyy (e.g., 31-12-2009). I am trying to use CONVERT to convert it to a DATE field. I was successful in converting a similar varchar field in the same table using the following:

SELECT DISTINCT(CONVERT(DATE, MYDATEFIELD1, 103)) AS [CONV_MYDATEFIELD1] FROM MYTABLE;

But when I apply the same to MYDATEFIELD2, which appears to have the same type of data values as MYDATEFIELD1, it fails with the following error:

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

I've tried sorting and using LIKE to try to find any characters that might prevent the conversion but I haven't been able to pinpoint anything.

Any help will be greatly appreciated. Thanks!

Upvotes: 0

Views: 1514

Answers (4)

user183121
user183121

Reputation: 13

I figured out the issue that was causing the CONVERT to fail but I'm not sure of the best way to select an answer (veritable stack noob) so, any help on that would be appreciated. Here are the major steps I took to find the issue:

  1. I used MIN and MAX SUBSTRING to identify that the component parts of the varchar field were correct (i.e., the 1st two digits min=01 max=31, middle two min=01 max=12)
  2. I used DISTINCT SUBSTRING to identify that all of the date separators were consistent (i.e., all dashes).
  3. I used MAX(LEN) to determine that my varchar "date" field was 12 characters (vs. the 10 characters I was expecting).
  4. I used CONVERT(VARBINARY, MYDATEFIELD2) to determine what was actually stored in the string.

The last step revealed that the field contained line feeds (00A). I opened the source text file in notepad++, clicked View -> Show Symbol -> Show All Characters and I could see the LF at the end of each line.

So now I'm modifying the DTSX package (fixed width text) to include an extra field for the linefeed that I can drop afterwards. Now that I know what the intended format of the date fields is, I'll try to import them as DT_DATE vs DT_STR. I'm not exactly sure how to specify the correct date style 105 at import (thanks @Panagiotis Kanavos) but I'll figure it out.

Whew! What a learning experience! :D

Thanks to everyone who helped - and if you can give advice on the best way to select the best answer it will be greatly appreciated.

Upvotes: 0

cloudsafe
cloudsafe

Reputation: 2504

Find your bad dates with the following:

SET DATEFORMAT dmy;

select MYDATEFIELD1, isdate(MYDATEFIELD1)
from MYDATEFIELD1

Upvotes: 0

Jason Boydell
Jason Boydell

Reputation: 86

If you can't find which value is causing the conversion error then use a cursor to go through all the records individually and use TRY CATCH to find which record(s) cause the conversion error. You could use a PRINT statement in the CATCH block to identify the records that are erroring.

Upvotes: 0

espino316
espino316

Reputation: 452

You may have some invalid dates (e.g. 30-02-2009), try to find them splitting the characters and validating the day and the months, assuring that the days correspond to the month and the month is in the range 01 - 12.

Upvotes: 1

Related Questions