0

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!

user183121
  • 13
  • 3
  • There is no such thing as a `yyyy-mm-dd date field`. Date types have no format. Formats come into play only when you want to create a string from a data for display, or parse a string into a date – Panagiotis Kanavos Oct 12 '17 at 15:42
  • Post the *actual* table schema and the *data*. The error says that the strings in the second field do not have the same format as the first field – Panagiotis Kanavos Oct 12 '17 at 15:43
  • Possible duplicate of [How to convert a "dd/mm/yyyy" string to datetime in SQL Server?](https://stackoverflow.com/questions/2780668/how-to-convert-a-dd-mm-yyyy-string-to-datetime-in-sql-server) – Tab Alleman Oct 12 '17 at 15:46
  • Or maybe it's more like this one: https://stackoverflow.com/questions/43765695/convert-dd-mm-yyyy-to-date-in-sql-server – Tab Alleman Oct 12 '17 at 15:47
  • Thank you for the comment Panagiotis. It caused me to look at the style codes more closely. – user183121 Oct 12 '17 at 15:57
  • Thank you for the comment Panagiotis. It caused me to look at the style codes more closely and I noticed something that may lead to a solution. That is, using style codes 103 (British/French) and 104 (German) convert MYDATEFIELD1 but, if I try 101 (US) the convert fails on MYDATEFIELD1 as well. – user183121 Oct 12 '17 at 16:03
  • @user183121 that's because the US style (MMDD) reverses the order of date, month. If the second field contains mixed styles, you ... ok, not good. *Some* dates will fail, like `12/31`, others will pass and return wrong values, eg `09/08`. You'll have to determine whether the fields contain the US style or not, whether the dates are mixed, or whether one filed is MMDD and the other DDMM before you start converting – Panagiotis Kanavos Oct 12 '17 at 16:08
  • @PanagiotisKanavos - I used min max substring to get the values in both date fields for the first two, middle two, and last 4 numbers and all 'passed' (i.e., first 2 were 1-31, middle two were 1-12, and last two were 2009 - 2016 (as expected for my dataset). There are some blanks in MyDateField1 but I don't think that's the issue. Maybe I can check the separator? – user183121 Oct 12 '17 at 17:12
  • Ok - I looked at the date separators, all are dashes. Then I ran this. SELECT(MAX(LEN(MyDateField1)) and got 10 but SELECT (MAX(LEN(MyDateFIeld2)) returns 12. I'm pretty sure that's a clue but I haven't been able to solve it yet. I tried LTRIM RTRIM but that doesn't fix it. – user183121 Oct 12 '17 at 18:08

4 Answers4

1

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.

espino316
  • 452
  • 4
  • 8
  • Or, install the Express edition of SQL Server 2017, copy the data and use `TRYPARSE` to find which rows have issues – Panagiotis Kanavos Oct 12 '17 at 15:45
  • I used substring to identify the min max values and it looks like the intended style of the MyDateField1 is DD-MM-YYYY – user183121 Oct 12 '17 at 16:49
  • And the same thing with MyDateField2 - the first two numbers are 01-31 and the second two numbers are 01-31. MyDateField1 has some rows with blank values. I guess looking at year is the last step. – user183121 Oct 12 '17 at 17:02
  • 01-31 is a format MM-DD not DD-MM, maybe you have values with both formats, that's why it fails. If this is the case, update those fields in the correct format. – espino316 Oct 12 '17 at 17:08
0

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.

Jason Boydell
  • 76
  • 1
  • 4
0

Find your bad dates with the following:

SET DATEFORMAT dmy;

select MYDATEFIELD1, isdate(MYDATEFIELD1)
from MYDATEFIELD1
cloudsafe
  • 2,444
  • 1
  • 8
  • 24
0

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.

user183121
  • 13
  • 3