March 6, 2019 at 3:42 am
Im currently working on updating company database in which on one of the tables the 'CreateDt' is in the nvarchar (20) type and stores dates in the following ways;
12-May-12
29/02/2011
9/24/2012
There are 17,000 rows
So,
Im currently working on code in which i will be able to convert these dates all into the one format such as DD/MM/YYYY
However Im struggling to find anything suitable'
Any help would be greatly appreciated!!
March 6, 2019 at 4:22 am
Hi as you have posted in the 2008 forum, I'm going to take it that is the version you are using.
From 2012 upwards there is a TRY_CONVERT built in fucntion
TRY_CONVERT
But as it's 2008 you have to do something like this;
WITH DateSamples
AS
(
SELECT '12-May-12' AS OriginalDate
UNION ALL
SELECT '29/02/2011'
UNION ALL
SELECT '9/24/2012'
UNION ALL
SELECT '28/02/2011'
)
SELECT OriginalDate,
CASE WHEN ISDATE(originalDate) = 1
THEN CAST(originalDate AS datetime)
ELSE
CASE WHEN SUBSTRING(originalDate, 3, 1) = '/'
THEN
CASE WHEN ISDATE(SUBSTRING(OriginalDate, 4, 2) + '/' + LEFT(originalDate, 2) + '/' + RIGHT (originalDate, 4)) = 1
THEN CAST(SUBSTRING(originalDate, 4, 2) + '/' + LEFT(originalDate, 2) + '/' + RIGHT (originalDate, 4) AS datetime)
END
END
END AS NewDate
FROM DateSamples;
And just build up all you different ways they have typed in dates from there.
One thing 2011 wasn't a leap year so 02/29/2011 is never going to work. You'll need to handle bad data like this.
Regards,
Rodders...
March 6, 2019 at 4:47 am
Rodders
Thank you very much, I will try this format!!
March 6, 2019 at 4:47 am
jwhite250297 - Wednesday, March 6, 2019 3:42 AMIm currently working on updating company database in which on one of the tables the 'CreateDt' is in the nvarchar (20) type and stores dates in the following ways;
12-May-12
29/02/2011
9/24/2012
There are 17,000 rows
So,
Im currently working on code in which i will be able to convert these dates all into the one format such as DD/MM/YYYYHowever Im struggling to find anything suitable'
Any help would be greatly appreciated!!
Don't fall into the trap of storing the dates in a string format. Store them as SMALLDATETIME data type. the presentation layer can format the date anyway that it likes
Create a new column [CreateDate] ALTER TABLE dbo.MyTable
ADD [CreateDate] SMALLDATETIME NULL;
Then update the new column using code like below. Keep modifying the case statement untill you have them all.
You will need to figure out the ambiguous dates, like '10/11/2012' - Is it "10 November" or "11 October"
UPDATE dbo.MyTable
SET [CreateDate] = CASE WHEN CreateDt LIKE '%[a-z][a-z][a-z]%' THEN CONVERT(SMALLDATETIME, CreateDt)
WHEN CreateDt LIKE '[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]%' THEN CONVERT(SMALLDATETIME, CreateDt, 121) --yyyy-mm-dd
WHEN CreateDt LIKE '[0-9][0-9][0-9][0-9]/[0-9][0-9]/[0-9][0-9]' THEN CONVERT(SMALLDATETIME, CreateDt, 111) --yyyy/mm/dd
WHEN CreateDt LIKE '[0-9][0-9]/[1][3-9]/[0-9][0-9][0-9][0-9]%' THEN CONVERT(SMALLDATETIME, CreateDt, 101) --mm/dd/yyyy
WHEN CreateDt LIKE '[0-9][0-9]/[2][0-9]/[0-9][0-9][0-9][0-9]%' THEN CONVERT(SMALLDATETIME, CreateDt, 101) --mm/dd/yyyy
WHEN CreateDt LIKE '[0-9][0-9]/[3][0-9]/[0-9][0-9][0-9][0-9]%' THEN CONVERT(SMALLDATETIME, CreateDt, 101) --mm/dd/yyyy
WHEN CreateDt LIKE '[0-9]/[1][3-9]/[0-9][0-9][0-9][0-9]%' THEN CONVERT(SMALLDATETIME, CreateDt, 101) --m/dd/yyyy
WHEN CreateDt LIKE '[0-9]/[2][0-9]/[0-9][0-9][0-9][0-9]%' THEN CONVERT(SMALLDATETIME, CreateDt, 101) --m/dd/yyyy
WHEN CreateDt LIKE '[0-9]/[3][0-9]/[0-9][0-9][0-9][0-9]%' THEN CONVERT(SMALLDATETIME, CreateDt, 101) --m/dd/yyyy
WHEN CreateDt LIKE '[1][3-9]/[0-9][0-9]/[0-9][0-9][0-9][0-9]%' THEN CONVERT(SMALLDATETIME, CreateDt, 103) --dd/mm/yyyy
WHEN CreateDt LIKE '[2][0-9]/[0-9][0-9]/[0-9][0-9][0-9][0-9]%' THEN CONVERT(SMALLDATETIME, CreateDt, 103) --dd/mm/yyyy
WHEN CreateDt LIKE '[3][0-9]/[0-9][0-9]/[0-9][0-9][0-9][0-9]%' THEN CONVERT(SMALLDATETIME, CreateDt, 103) --dd/mm/yyyy
WHEN CreateDt LIKE '[1][3-9]/[0-9]/[0-9][0-9][0-9][0-9]%' THEN CONVERT(SMALLDATETIME, CreateDt, 103) --dd/m/yyyy
WHEN CreateDt LIKE '[2][0-9]/[0-9]/[0-9][0-9][0-9][0-9]%' THEN CONVERT(SMALLDATETIME, CreateDt, 103) --dd/m/yyyy
WHEN CreateDt LIKE '[3][0-9]/[0-9]/[0-9][0-9][0-9][0-9]%' THEN CONVERT(SMALLDATETIME, CreateDt, 103) --dd/m/yyyy
END
WHERE [CreateDate] IS NULL;
March 6, 2019 at 4:55 am
jwhite250297 - Wednesday, March 6, 2019 4:47 AMRodders
Your help is greatly appreciated however for example dates
such as '24/04/2006 17:17:19'
appears as a NULL NewDate
what changes need made to solve this issue
Regards,
JW
FYI. That is a datetime, not a smalldatetime. A conversion to smalldatetime will round the seconds. ( > 29.998 seconds will round up)
March 6, 2019 at 5:14 am
Researching into the unambiguous dates and asking fellow colleagues are they aware of which dates are which
March 6, 2019 at 5:15 am
If you have date 11/12/2013 how do you know if it's dd/mm/yyyy or mm/dd/yyyy?
March 6, 2019 at 5:27 am
DesNorton - Wednesday, March 6, 2019 4:47 AMjwhite250297 - Wednesday, March 6, 2019 3:42 AMIm currently working on updating company database in which on one of the tables the 'CreateDt' is in the nvarchar (20) type and stores dates in the following ways;
12-May-12
29/02/2011
9/24/2012
There are 17,000 rows
So,
Im currently working on code in which i will be able to convert these dates all into the one format such as DD/MM/YYYYHowever Im struggling to find anything suitable'
Any help would be greatly appreciated!!
Don't fall into the trap of storing the dates in a string format. Store them as SMALLDATETIME data type. the presentation layer can format the date anyway that it likes
Create a new column [CreateDate]
ALTER TABLE dbo.MyTable
ADD [CreateDate] SMALLDATETIME NULL;Then update the new column using code like below. Keep modifying the case statement untill you have them all.
You will need to figure out the ambiguous dates, like '10/11/2012' - Is it "10 November" or "11 October"
UPDATE dbo.MyTable
SET [CreateDate] = CASE WHEN CreateDt LIKE '%[a-z][a-z][a-z]%' THEN CONVERT(SMALLDATETIME, CreateDt)
WHEN CreateDt LIKE '[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]%' THEN CONVERT(SMALLDATETIME, CreateDt, 121) --yyyy-mm-dd
WHEN CreateDt LIKE '[0-9][0-9][0-9][0-9]/[0-9][0-9]/[0-9][0-9]' THEN CONVERT(SMALLDATETIME, CreateDt, 111) --yyyy/mm/dd
WHEN CreateDt LIKE '[0-9][0-9]/[1][3-9]/[0-9][0-9][0-9][0-9]%' THEN CONVERT(SMALLDATETIME, CreateDt, 101) --mm/dd/yyyy
WHEN CreateDt LIKE '[0-9][0-9]/[2][0-9]/[0-9][0-9][0-9][0-9]%' THEN CONVERT(SMALLDATETIME, CreateDt, 101) --mm/dd/yyyy
WHEN CreateDt LIKE '[0-9][0-9]/[3][0-9]/[0-9][0-9][0-9][0-9]%' THEN CONVERT(SMALLDATETIME, CreateDt, 101) --mm/dd/yyyy
WHEN CreateDt LIKE '[0-9]/[1][3-9]/[0-9][0-9][0-9][0-9]%' THEN CONVERT(SMALLDATETIME, CreateDt, 101) --m/dd/yyyy
WHEN CreateDt LIKE '[0-9]/[2][0-9]/[0-9][0-9][0-9][0-9]%' THEN CONVERT(SMALLDATETIME, CreateDt, 101) --m/dd/yyyy
WHEN CreateDt LIKE '[0-9]/[3][0-9]/[0-9][0-9][0-9][0-9]%' THEN CONVERT(SMALLDATETIME, CreateDt, 101) --m/dd/yyyy
WHEN CreateDt LIKE '[1][3-9]/[0-9][0-9]/[0-9][0-9][0-9][0-9]%' THEN CONVERT(SMALLDATETIME, CreateDt, 103) --dd/mm/yyyy
WHEN CreateDt LIKE '[2][0-9]/[0-9][0-9]/[0-9][0-9][0-9][0-9]%' THEN CONVERT(SMALLDATETIME, CreateDt, 103) --dd/mm/yyyy
WHEN CreateDt LIKE '[3][0-9]/[0-9][0-9]/[0-9][0-9][0-9][0-9]%' THEN CONVERT(SMALLDATETIME, CreateDt, 103) --dd/mm/yyyy
WHEN CreateDt LIKE '[1][3-9]/[0-9]/[0-9][0-9][0-9][0-9]%' THEN CONVERT(SMALLDATETIME, CreateDt, 103) --dd/m/yyyy
WHEN CreateDt LIKE '[2][0-9]/[0-9]/[0-9][0-9][0-9][0-9]%' THEN CONVERT(SMALLDATETIME, CreateDt, 103) --dd/m/yyyy
WHEN CreateDt LIKE '[3][0-9]/[0-9]/[0-9][0-9][0-9][0-9]%' THEN CONVERT(SMALLDATETIME, CreateDt, 103) --dd/m/yyyy
END
WHERE [CreateDate] IS NULL;
Greatly appreciate the help Des,
However I'm getting a error message as follows;
Msg 295, Level 16, State 3, Line 1
Conversion failed when converting character string to smalldatetime data type.
Any ideas to what the issue maybe?
March 6, 2019 at 6:35 am
UCDA2019 - Wednesday, March 6, 2019 3:42 AMIm currently working on updating company database in which on one of the tables the 'CreateDt' is in the nvarchar (20) type and stores dates in the following ways;
12-May-12
29/02/2011
9/24/2012
There are 17,000 rows
So,
Im currently working on code in which i will be able to convert these dates all into the one format such as DD/MM/YYYYHowever Im struggling to find anything suitable'
Any help would be greatly appreciated!!
If you really do have dates s 29/02/2011 and 9/24/2012 in your table, then your task is impossible. These two sample dates can be safely converted because the day-of-month component is larger than the maximum permitted month number. About a third of dates will have the opposite - and you have no means of determining which part of the string is day-of-month, and which part is month number.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 6, 2019 at 6:49 am
UCDA2019 - Wednesday, March 6, 2019 5:27 AMGreatly appreciate the help Des,
However I'm getting a error message as follows;Msg 295, Level 16, State 3, Line 1
Conversion failed when converting character string to smalldatetime data type.Any ideas to what the issue maybe?
I cannot determine the cause of the issue as I cannot see your data.
That said, I would suggest commenting out half of the CASE options and trying again.
Then keep halving them, until you find the CASE that is bringing back data that is causing the error.
Once you have a way to identify the bad data, then change the UPDATE into a SELECT, and manually inspect the data.
March 6, 2019 at 7:09 am
rodjkidd - Wednesday, March 6, 2019 4:22 AMHi as you have posted in the 2008 forum, I'm going to take it that is the version you are using.
From 2012 upwards there is a TRY_CONVERT built in fucntion
TRY_CONVERTBut as it's 2008 you have to do something like this;
WITH DateSamples
AS
(
SELECT '12-May-12' AS OriginalDate
UNION ALL
SELECT '29/02/2011'
UNION ALL
SELECT '9/24/2012'
UNION ALL
SELECT '28/02/2011'
)
SELECT OriginalDate,
CASE WHEN ISDATE(originalDate) = 1
THEN CAST(originalDate AS datetime)
ELSE
CASE WHEN SUBSTRING(originalDate, 3, 1) = '/'
THEN
CASE WHEN ISDATE(SUBSTRING(OriginalDate, 4, 2) + '/' + LEFT(originalDate, 2) + '/' + RIGHT (originalDate, 4)) = 1
THEN CAST(SUBSTRING(originalDate, 4, 2) + '/' + LEFT(originalDate, 2) + '/' + RIGHT (originalDate, 4) AS datetime)
END
END
END AS NewDate
FROM DateSamples;And just build up all you different ways they have typed in dates from there.
One thing 2011 wasn't a leap year so 02/29/2011 is never going to work. You'll need to handle bad data like this.Regards,
Rodders...
Rodders
Your help is greatly appreciated however for example dates
such as '24/04/2006 17:17:19'
appears as a NULL NewDate
what changes need made to solve this issue?
Regards,
UC
March 6, 2019 at 8:35 am
DesNorton - Wednesday, March 6, 2019 6:49 AMUCDA2019 - Wednesday, March 6, 2019 5:27 AMGreatly appreciate the help Des,
However I'm getting a error message as follows;Msg 295, Level 16, State 3, Line 1
Conversion failed when converting character string to smalldatetime data type.Any ideas to what the issue maybe?
I cannot determine the cause of the issue as I cannot see your data.
That said, I would suggest commenting out half of the CASE options and trying again.
Then keep halving them, until you find the CASE that is bringing back data that is causing the error.Once you have a way to identify the bad data, then change the UPDATE into a SELECT, and manually inspect the data.
If you have a value of 29/02/2011, then the conversion will fail, as there is no 29 Feb 2011.
The following code has 2 extra CASEs to set 29Feb values to 1900-01-01. You can then later manually validate and update them.UPDATE dbo.MyTable
SET [CreateDate] = CASE WHEN CreateDt LIKE '%[a-z][a-z][a-z]%' THEN CONVERT(SMALLDATETIME, CreateDt)
WHEN CreateDt LIKE '29/02/[0-9][0-9][0-9][0-9]%' THEN '1900-01-01' -- Special case to filter out 29 Feb
WHEN CreateDt LIKE '29-02-[0-9][0-9][0-9][0-9]%' THEN '1900-01-01' -- Special case to filter out 29 Feb
WHEN CreateDt LIKE '[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]%' THEN CONVERT(SMALLDATETIME, CreateDt, 121) --yyyy-mm-dd
WHEN CreateDt LIKE '[0-9][0-9][0-9][0-9]/[0-9][0-9]/[0-9][0-9]' THEN CONVERT(SMALLDATETIME, CreateDt, 111) --yyyy/mm/dd
WHEN CreateDt LIKE '[0-9][0-9]/[1][3-9]/[0-9][0-9][0-9][0-9]%' THEN CONVERT(SMALLDATETIME, CreateDt, 101) --mm/dd/yyyy
WHEN CreateDt LIKE '[0-9][0-9]/[2][0-9]/[0-9][0-9][0-9][0-9]%' THEN CONVERT(SMALLDATETIME, CreateDt, 101) --mm/dd/yyyy
WHEN CreateDt LIKE '[0-9][0-9]/[3][0-9]/[0-9][0-9][0-9][0-9]%' THEN CONVERT(SMALLDATETIME, CreateDt, 101) --mm/dd/yyyy
WHEN CreateDt LIKE '[0-9]/[1][3-9]/[0-9][0-9][0-9][0-9]%' THEN CONVERT(SMALLDATETIME, CreateDt, 101) --m/dd/yyyy
WHEN CreateDt LIKE '[0-9]/[2][0-9]/[0-9][0-9][0-9][0-9]%' THEN CONVERT(SMALLDATETIME, CreateDt, 101) --m/dd/yyyy
WHEN CreateDt LIKE '[0-9]/[3][0-9]/[0-9][0-9][0-9][0-9]%' THEN CONVERT(SMALLDATETIME, CreateDt, 101) --m/dd/yyyy
WHEN CreateDt LIKE '[1][3-9]/[0-9][0-9]/[0-9][0-9][0-9][0-9]%' THEN CONVERT(SMALLDATETIME, CreateDt, 103) --dd/mm/yyyy
WHEN CreateDt LIKE '[2][0-9]/[0-9][0-9]/[0-9][0-9][0-9][0-9]%' THEN CONVERT(SMALLDATETIME, CreateDt, 103) --dd/mm/yyyy
WHEN CreateDt LIKE '[3][0-9]/[0-9][0-9]/[0-9][0-9][0-9][0-9]%' THEN CONVERT(SMALLDATETIME, CreateDt, 103) --dd/mm/yyyy
WHEN CreateDt LIKE '[1][3-9]/[0-9]/[0-9][0-9][0-9][0-9]%' THEN CONVERT(SMALLDATETIME, CreateDt, 103) --dd/m/yyyy
WHEN CreateDt LIKE '[2][0-9]/[0-9]/[0-9][0-9][0-9][0-9]%' THEN CONVERT(SMALLDATETIME, CreateDt, 103) --dd/m/yyyy
WHEN CreateDt LIKE '[3][0-9]/[0-9]/[0-9][0-9][0-9][0-9]%' THEN CONVERT(SMALLDATETIME, CreateDt, 103) --dd/m/yyyy
END
WHERE [CreateDate] IS NULL;
March 6, 2019 at 8:43 am
JW,
As others have said you just have a mess of bad data there.
Using ISDATE can get round the fact that someone has put in a Feb 29 for a year that wasn't a leap year, but its also very strict on what is a date. As you didn't have times in your sample data I didn't consider it.
ISDATE ('24/04/2006 17:17:19') isn't a date
but
ISDATE ('2006/04/24 17:17:19') is a date.
Des's solution is very neat but the Feb 29 2011 is no doubt causing it problems.
I'll have another look at this tonight if I get a chance. But you have too many exceptions.
Personally I take the approach of adding a new column of a datetime type. and using Des solution get as many valid dates in there as possible. Then depending on the number remaining, fix by hand or leave them as NULL or 19000101.
Rodders...
March 6, 2019 at 8:54 am
Having done some digging the Feb 29 2011 is an error and has been removed from the table.
March 6, 2019 at 8:58 am
UCDA2019 - Wednesday, March 6, 2019 8:54 AMHaving done some digging the Feb 29 2011 is an error and has been removed from the table.
What about the other issue? What date is 11/12/2013? Is it December 11, 2013 or November 12, 2013?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 15 posts - 1 through 15 (of 37 total)
You must be logged in to reply to this topic. Login to reply