January 16, 2012 at 7:20 am
Hi,
Data was captured incorrectly and is in a numeric format. I have Googled and received some potential solutions but the error message received is 'The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.'
Here is some sample data with the results I want. If there is null or 0 in a column it must be defaulted to 1900-01-01. Keep in mind that I will then be performing calculations such as subtracting this date column from another one that is in the correct format.
--DROP TABLE #Dates
CREATE TABLE #Dates (CurrentDateFormat int, IncidentDate Date )
INSERT INTO #Dates VALUES (0,'1900-01-01') --All 0's to default to '1900-01-01'
INSERT INTO #Dates VALUES (14041979,'1979-04-14')
INSERT INTO #Dates VALUES (04021983,'1983-02-04')
INSERT INTO #Dates VALUES (18021982,'1982-02-18')
INSERT INTO #Dates VALUES (01011974,'1974-01-01')
INSERT INTO #Dates VALUES (29011969,'1969-01-29')
select * from #Dates
Thanks in advance
January 16, 2012 at 7:38 am
BEGIN TRAN
CREATE TABLE #Dates (CurrentDateFormat int, IncidentDate Date )
INSERT INTO #Dates VALUES (0,'1900-01-01') --All 0's to default to '1900-01-01'
INSERT INTO #Dates VALUES (14041979,'1979-04-14')
INSERT INTO #Dates VALUES (04021983,'1983-02-04')
INSERT INTO #Dates VALUES (18021982,'1982-02-18')
INSERT INTO #Dates VALUES (01011974,'1974-01-01')
INSERT INTO #Dates VALUES (29011969,'1969-01-29')
INSERT INTO #Dates VALUES (NULL,'1900-01-01')
SELECT a.CurrentDateFormat, a.IncidentDate,
CAST(SUBSTRING(charDateFormat,5,4)+'-'+SUBSTRING(charDateFormat,3,2)+'-'+SUBSTRING(charDateFormat,1,2) AS DATETIME)
FROM #Dates a
CROSS APPLY (SELECT CASE WHEN CurrentDateFormat IS NULL OR CurrentDateFormat = 0
THEN '01011900'
WHEN LEN(CAST(CurrentDateFormat AS VARCHAR(8))) = 7
THEN '0'+CAST(CurrentDateFormat AS VARCHAR(8))
ELSE CAST(CurrentDateFormat AS VARCHAR(8)) END) b(charDateFormat)
ROLLBACK
The above is assuming your sample data is representative of how your actual data is. However, I think it probably isn't since you can't start an INT with a 0.
January 16, 2012 at 8:01 am
Thanks for the reply:
I receive the error message: 'Msg 242, Level 16, State 3, Line 1
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
'
The query does however work for about 400 of the 42 000 records when I run it.
I did try check to see if there were any inconsistencies in length with the following:
SELECT distinct DATALENGTH(incident_driver_dob) AS TEXTFieldSize
from #Dates
and received '1' and '8' as expected. I have cleaned up the data, there was some text in the fields before. Could there possible be a problem with the spacing?
Thanks
January 16, 2012 at 8:06 am
The most likely issue is that the data has been entered in multiple formats, e.g. DDMMYYYY and MMDDYYYY
This makes the whole task harder because you can't know which way you should convert 01052012, should that be 1st May or 5th Jan?
Try this: -
SELECT *
FROM (SELECT a.CurrentDateFormat, a.IncidentDate,
ISDATE(SUBSTRING(charDateFormat,5,4)+'-'+SUBSTRING(charDateFormat,3,2)+'-'+SUBSTRING(charDateFormat,1,2)) AS dateCheck
FROM #Dates a
CROSS APPLY (SELECT CASE WHEN CurrentDateFormat IS NULL OR CurrentDateFormat = 0
THEN '01011900'
WHEN LEN(CAST(CurrentDateFormat AS VARCHAR(8))) = 7
THEN '0'+CAST(CurrentDateFormat AS VARCHAR(8))
ELSE CAST(CurrentDateFormat AS VARCHAR(8)) END) b(charDateFormat)) innerQ
WHERE dateCheck <> 1
ISDATE is a bit broken, but it may point you at some incorrect dates.
January 16, 2012 at 8:22 am
I think I have found the problem. There are 64 dates that have a problem. These can be defaulted to null. The leading zero disappears when you create the table but you can see the problem. Is there a smart case statement that can solve this without me explicitly having to make a condition for each of these.
CREATE TABLE #Dates (CurrentDateFormat int, DateCheck int )
INSERT INTO #Dates VALUES (05040167,0)
INSERT INTO #Dates VALUES (21080987,0)
INSERT INTO #Dates VALUES (22100198,0)
INSERT INTO #Dates VALUES (04040972,0)
INSERT INTO #Dates VALUES (01100196,0)
INSERT INTO #Dates VALUES (24030185,0)
INSERT INTO #Dates VALUES (25121096,0)
INSERT INTO #Dates VALUES (09070983,0)
INSERT INTO #Dates VALUES (07120185,0)
INSERT INTO #Dates VALUES (19060167,0)
INSERT INTO #Dates VALUES (01011111,0)
INSERT INTO #Dates VALUES (07090182,0)
INSERT INTO #Dates VALUES (13090177,0)
INSERT INTO #Dates VALUES (10060196,0)
INSERT INTO #Dates VALUES (10100197,0)
INSERT INTO #Dates VALUES (21051286,0)
INSERT INTO #Dates VALUES (01010974,0)
INSERT INTO #Dates VALUES (06040164,0)
INSERT INTO #Dates VALUES (24050165,0)
INSERT INTO #Dates VALUES (07100199,0)
INSERT INTO #Dates VALUES (28061658,0)
INSERT INTO #Dates VALUES (16061676,0)
INSERT INTO #Dates VALUES (25110984,0)
INSERT INTO #Dates VALUES (17121673,0)
INSERT INTO #Dates VALUES (16021081,0)
INSERT INTO #Dates VALUES (03120981,0)
INSERT INTO #Dates VALUES (05050196,0)
INSERT INTO #Dates VALUES (29090194,0)
INSERT INTO #Dates VALUES (03120198,0)
INSERT INTO #Dates VALUES (01061674,0)
INSERT INTO #Dates VALUES (13031657,0)
INSERT INTO #Dates VALUES (27070192,0)
INSERT INTO #Dates VALUES (06061645,0)
INSERT INTO #Dates VALUES (19060181,0)
INSERT INTO #Dates VALUES (22080198,0)
INSERT INTO #Dates VALUES (08020980,0)
INSERT INTO #Dates VALUES (06100984,0)
INSERT INTO #Dates VALUES (07030194,0)
INSERT INTO #Dates VALUES (05050137,0)
INSERT INTO #Dates VALUES (19060182,0)
INSERT INTO #Dates VALUES (18090187,0)
INSERT INTO #Dates VALUES (11060196,0)
INSERT INTO #Dates VALUES (14071656,0)
INSERT INTO #Dates VALUES (31100192,0)
INSERT INTO #Dates VALUES (06081673,0)
INSERT INTO #Dates VALUES (10020182,0)
INSERT INTO #Dates VALUES (25070198,0)
INSERT INTO #Dates VALUES (03120164,0)
INSERT INTO #Dates VALUES (24080179,0)
INSERT INTO #Dates VALUES (29070184,0)
INSERT INTO #Dates VALUES (27030983,0)
INSERT INTO #Dates VALUES (16090188,0)
INSERT INTO #Dates VALUES (16090188,0)
INSERT INTO #Dates VALUES (27100982,0)
INSERT INTO #Dates VALUES (30041672,0)
INSERT INTO #Dates VALUES (20010186,0)
INSERT INTO #Dates VALUES (28110196,0)
INSERT INTO #Dates VALUES (06071655,0)
INSERT INTO #Dates VALUES (03040973,0)
INSERT INTO #Dates VALUES (15090181,0)
INSERT INTO #Dates VALUES (14100980,0)
INSERT INTO #Dates VALUES (02100196,0)
INSERT INTO #Dates VALUES (30080197,0)
INSERT INTO #Dates VALUES (04100165,0)
select * from #Dates
January 16, 2012 at 8:23 am
I think you need to fix your sample data as this values are certainly not integers, which means any solution we give is going to be incorrect. What is the DDL for your actual table?
Jared
CE - Microsoft
January 16, 2012 at 8:32 am
mic.con87 (1/16/2012)
I think I have found the problem. There are 64 dates that have a problem. These can be defaulted to null. The leading zero disappears when you create the table but you can see the problem. Is there a smart case statement that can solve this without me explicitly having to make a condition for each of these.
CREATE TABLE #Dates (CurrentDateFormat int, DateCheck int )
INSERT INTO #Dates VALUES (05040167,0)
INSERT INTO #Dates VALUES (21080987,0)
INSERT INTO #Dates VALUES (22100198,0)
INSERT INTO #Dates VALUES (04040972,0)
INSERT INTO #Dates VALUES (01100196,0)
INSERT INTO #Dates VALUES (24030185,0)
INSERT INTO #Dates VALUES (25121096,0)
INSERT INTO #Dates VALUES (09070983,0)
INSERT INTO #Dates VALUES (07120185,0)
INSERT INTO #Dates VALUES (19060167,0)
INSERT INTO #Dates VALUES (01011111,0)
INSERT INTO #Dates VALUES (07090182,0)
INSERT INTO #Dates VALUES (13090177,0)
INSERT INTO #Dates VALUES (10060196,0)
INSERT INTO #Dates VALUES (10100197,0)
INSERT INTO #Dates VALUES (21051286,0)
INSERT INTO #Dates VALUES (01010974,0)
INSERT INTO #Dates VALUES (06040164,0)
INSERT INTO #Dates VALUES (24050165,0)
INSERT INTO #Dates VALUES (07100199,0)
INSERT INTO #Dates VALUES (28061658,0)
INSERT INTO #Dates VALUES (16061676,0)
INSERT INTO #Dates VALUES (25110984,0)
INSERT INTO #Dates VALUES (17121673,0)
INSERT INTO #Dates VALUES (16021081,0)
INSERT INTO #Dates VALUES (03120981,0)
INSERT INTO #Dates VALUES (05050196,0)
INSERT INTO #Dates VALUES (29090194,0)
INSERT INTO #Dates VALUES (03120198,0)
INSERT INTO #Dates VALUES (01061674,0)
INSERT INTO #Dates VALUES (13031657,0)
INSERT INTO #Dates VALUES (27070192,0)
INSERT INTO #Dates VALUES (06061645,0)
INSERT INTO #Dates VALUES (19060181,0)
INSERT INTO #Dates VALUES (22080198,0)
INSERT INTO #Dates VALUES (08020980,0)
INSERT INTO #Dates VALUES (06100984,0)
INSERT INTO #Dates VALUES (07030194,0)
INSERT INTO #Dates VALUES (05050137,0)
INSERT INTO #Dates VALUES (19060182,0)
INSERT INTO #Dates VALUES (18090187,0)
INSERT INTO #Dates VALUES (11060196,0)
INSERT INTO #Dates VALUES (14071656,0)
INSERT INTO #Dates VALUES (31100192,0)
INSERT INTO #Dates VALUES (06081673,0)
INSERT INTO #Dates VALUES (10020182,0)
INSERT INTO #Dates VALUES (25070198,0)
INSERT INTO #Dates VALUES (03120164,0)
INSERT INTO #Dates VALUES (24080179,0)
INSERT INTO #Dates VALUES (29070184,0)
INSERT INTO #Dates VALUES (27030983,0)
INSERT INTO #Dates VALUES (16090188,0)
INSERT INTO #Dates VALUES (16090188,0)
INSERT INTO #Dates VALUES (27100982,0)
INSERT INTO #Dates VALUES (30041672,0)
INSERT INTO #Dates VALUES (20010186,0)
INSERT INTO #Dates VALUES (28110196,0)
INSERT INTO #Dates VALUES (06071655,0)
INSERT INTO #Dates VALUES (03040973,0)
INSERT INTO #Dates VALUES (15090181,0)
INSERT INTO #Dates VALUES (14100980,0)
INSERT INTO #Dates VALUES (02100196,0)
INSERT INTO #Dates VALUES (30080197,0)
INSERT INTO #Dates VALUES (04100165,0)
select * from #Dates
If this sample data is what you are getting when you select (02100196 for example) then the column is NOT an int. Please let us know what the actual data type for this column is. Also, what are these strings format? Using the example 02100196, what should this be for the date and why?
Jared
CE - Microsoft
January 16, 2012 at 8:32 am
Sorry I actually have no idea how to get the DDL. I'm pretty new to SQL. When I look at the table design the column has data type varchar(2000) if that helps.
January 16, 2012 at 8:36 am
These are 64 cases out of the 42 000 that need to be defaulted to '01-01-1900' as there is no way of finding out what the correct date should have been. I want to run the previous solution supplied as well as a clause where these cases will be defaulted to '01-01-1900'.
Thanks
January 16, 2012 at 8:42 am
mic.con87 (1/16/2012)
I think I have found the problem. There are 64 dates that have a problem. These can be defaulted to null. The leading zero disappears when you create the table but you can see the problem. Is there a smart case statement that can solve this without me explicitly having to make a condition for each of these.
Try this: -
SELECT a.CurrentDateFormat,
CAST (CASE WHEN ISDATE(SUBSTRING(charDateFormat,5,4)+'-'+SUBSTRING(charDateFormat,3,2)+'-'+SUBSTRING(charDateFormat,1,2)) <> 1
THEN '1900-01-01'
ELSE SUBSTRING(charDateFormat,5,4)+'-'+SUBSTRING(charDateFormat,3,2)+'-'+SUBSTRING(charDateFormat,1,2) END AS DATETIME)
FROM #Dates a
CROSS APPLY (SELECT CASE WHEN CurrentDateFormat IS NULL OR CurrentDateFormat = 0
THEN '01011900'
WHEN LEN(CAST(CurrentDateFormat AS VARCHAR(8))) = 7
THEN '0'+CAST(CurrentDateFormat AS VARCHAR(8))
ELSE CAST(CurrentDateFormat AS VARCHAR(8)) END) b(charDateFormat)
January 16, 2012 at 10:52 pm
Cadavre thanks so much for you help!!! Your code is brilliant and very efficient 😀
If you do have time please can you explain the CROSS APPLY, LEN and also why you used a CAST and not a CONVERT. I'm really keen on improving my own skills.
Thanks again!!!
January 17, 2012 at 12:37 am
mic.con87 (1/16/2012)
please can you explain the CROSS APPLY...
See:
http://qa.sqlservercentral.com/articles/APPLY/69953/
http://qa.sqlservercentral.com/articles/APPLY/69954/
LEN
http://msdn.microsoft.com/en-us/library/ms190329.aspx
...and also why you used a CAST and not a CONVERT.
I tend to prefer CONVERT because it supports explicit styles. Cadavre may have his reasons though.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
January 17, 2012 at 2:07 am
SQL Kiwi (1/17/2012)
mic.con87 (1/16/2012)
please can you explain the CROSS APPLY...See:
http://qa.sqlservercentral.com/articles/APPLY/69953/
http://qa.sqlservercentral.com/articles/APPLY/69954/
LEN
http://msdn.microsoft.com/en-us/library/ms190329.aspx
...and also why you used a CAST and not a CONVERT.
I tend to prefer CONVERT because it supports explicit styles. Cadavre may have his reasons though.
Thanks Paul, your articles are exactly where I started to learn how APPLY works.
As for CAST vs CONVERT; it's generally a preference thing. I work with SQL Server and PostgreSQL, CAST is the ANSI standard so is the same between the two which tends to make me use it.
mic.con87 (1/16/2012)
Cadavre thanks so much for you help!!! Your code is brilliant and very efficient 😀If you do have time please can you explain the CROSS APPLY, LEN and also why you used a CAST and not a CONVERT. I'm really keen on improving my own skills.
Thanks again!!!
Sorry, missed that you asked why I grabbed the LEN (length of the string). This is because in your OP you said that the datatype was INT, and if you insert a number of "01012011" into an INT then only "1012011" would be stored. I grabbed the length to append a 0 where the string was less than 8 characters (could have done the same with RIGHT).
January 17, 2012 at 6:59 am
Thank you both for your help and explanations, appreciate it.
January 17, 2012 at 12:36 pm
with
q as (select CurrentDateFormat,right(CAST(CurrentDateFormat as varchar(8)),4) +
left(right(CAST(CurrentDateFormat as varchar(8)),6),2) +
left(CurrentDateFormat,len(CAST (CurrentDateFormat as varchar(8)))-6) d
from #Dates d
)
select CurrentDateFormat,cast(case when ISDATE(d)=1 then d else '19000101' end as datetime)
from q
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply