May 28, 2015 at 6:38 am
Dear All,
How can we identify the Date Format from a String in SQL Server.
I might get an input from external source as "MM-DD-YYYY" or "DD-MM-YYYY" or "YYYY-MM-DD" or "YYYY-DD-MM", all i have to do is return the same with Current Date in the same format as Input.
Ex :
1. "02-20-2013" -> "05-28-2015"
2. "2014-04-19" -> "2015-05-28"
Any help would be highly appreciated. Any method to identify the DateFormat then it would have made the job very easy. :ermm:
May 28, 2015 at 7:05 am
AswinKrishnan (5/28/2015)
Dear All,How can we identify the Date Format from a String in SQL Server.
I might get an input from external source as "MM-DD-YYYY" or "DD-MM-YYYY" or "YYYY-MM-DD" or "YYYY-DD-MM", all i have to do is return the same with Current Date in the same format as Input.
Ex :
1. "02-20-2013" -> "05-28-2015"
2. "2014-04-19" -> "2015-05-28"
Any help would be highly appreciated. Any method to identify the DateFormat then it would have made the job very easy. :ermm:
You really can't tell from a string which is which. It is obvious from the examples you posted but consider something like '2015-02-05' or '02-03-2015'. If you don't know the format and it is a string you are kind of stuck. You can do some trial and error to see which format is able to be parsed but that doesn't mean you have it correct.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 28, 2015 at 10:21 am
It's easy to identify "DD-MM-YYYY" and "MM-DD-YYYY" from "YYYY-MM-DD" or "YYYY-DD-MM", as long as you always use dashes or slashes. The problem comes to differentiating months against days when days are less than 12.
If you want to be sure about the date format, ask the source or use date data types if possible.
If those options aren't possible, you could try something like this:
CREATE TABLE #SampleDates(
chardate char(10))
INSERT INTO #SampleDates
VALUES( '01-02-2015'),
( '21-02-2015'),
( '11-22-2015'),
( '2015-01-05'),
( '2015-01-15'),
( '2015-31-05'),
( '2015-21-25')
SELECT CASE WHEN chardate LIKE '0[0-9]-[2-3][0-9]-[1-2][0-9][0-9][0-9]'
OR chardate LIKE '1[0-2]-[2-3][0-9]-[1-2][0-9][0-9][0-9]'
OR chardate LIKE '0[0-9]-1[3-9]-[1-2][0-9][0-9][0-9]'
OR chardate LIKE '1[0-2]-1[3-9]-[1-2][0-9][0-9][0-9]'
THEN 'Possible MM-DD-YYYY'
WHEN chardate LIKE '[2-3][0-9]-0[0-9]-[1-2][0-9][0-9][0-9]'
OR chardate LIKE '[2-3][0-9]-1[0-2]-[1-2][0-9][0-9][0-9]'
OR chardate LIKE '1[3-9]-0[0-9]-[1-2][0-9][0-9][0-9]'
OR chardate LIKE '1[3-9]-1[0-2]-[1-2][0-9][0-9][0-9]'
THEN 'Possible DD-MM-YYYY'
WHEN chardate LIKE '[1-2][0-9][0-9][0-9]-[2-3][0-9]-0[0-9]'
OR chardate LIKE '[1-2][0-9][0-9][0-9]-[2-3][0-9]-1[0-2]'
OR chardate LIKE '[1-2][0-9][0-9][0-9]-1[3-9]-0[0-9]'
OR chardate LIKE '[1-2][0-9][0-9][0-9]-1[3-9]-1[0-2]'
THEN 'Possible YYYY-DD-MM'
WHEN chardate LIKE '[1-2][0-9][0-9][0-9]-0[0-9]-[2-3][0-9]'
OR chardate LIKE '[1-2][0-9][0-9][0-9]-1[0-2]-[2-3][0-9]'
OR chardate LIKE '[1-2][0-9][0-9][0-9]-0[0-9]-1[3-9]'
OR chardate LIKE '[1-2][0-9][0-9][0-9]-1[0-2]-1[3-9]'
THEN 'Possible YYYY-MM-DD'
ELSE 'Unknown' END Date_Format,
chardate
FROM #SampleDates
SELECT CASE WHEN chardate LIKE '0[0-9]-[2-3][0-9]-[1-2][0-9][0-9][0-9]'
OR chardate LIKE '1[0-2]-[2-3][0-9]-[1-2][0-9][0-9][0-9]'
OR chardate LIKE '0[0-9]-1[3-9]-[1-2][0-9][0-9][0-9]'
OR chardate LIKE '1[0-2]-1[3-9]-[1-2][0-9][0-9][0-9]'
THEN 'Possible MM-DD-YYYY'
WHEN chardate LIKE '[2-3][0-9]-0[0-9]-[1-2][0-9][0-9][0-9]'
OR chardate LIKE '[2-3][0-9]-1[0-2]-[1-2][0-9][0-9][0-9]'
OR chardate LIKE '1[3-9]-0[0-9]-[1-2][0-9][0-9][0-9]'
OR chardate LIKE '1[3-9]-1[0-2]-[1-2][0-9][0-9][0-9]'
THEN 'Possible DD-MM-YYYY'
WHEN chardate LIKE '[1-2][0-9][0-9][0-9]-[2-3][0-9]-0[0-9]'
OR chardate LIKE '[1-2][0-9][0-9][0-9]-[2-3][0-9]-1[0-2]'
OR chardate LIKE '[1-2][0-9][0-9][0-9]-1[3-9]-0[0-9]'
OR chardate LIKE '[1-2][0-9][0-9][0-9]-1[3-9]-1[0-2]'
THEN 'Possible YYYY-DD-MM'
WHEN chardate LIKE '[1-2][0-9][0-9][0-9]-0[0-9]-[2-3][0-9]'
OR chardate LIKE '[1-2][0-9][0-9][0-9]-1[0-2]-[2-3][0-9]'
OR chardate LIKE '[1-2][0-9][0-9][0-9]-0[0-9]-1[3-9]'
OR chardate LIKE '[1-2][0-9][0-9][0-9]-1[0-2]-1[3-9]'
THEN 'Possible YYYY-MM-DD'
ELSE 'Unknown' END Date_Format,
COUNT(*)
FROM #SampleDates
GROUP BY CASE WHEN chardate LIKE '0[0-9]-[2-3][0-9]-[1-2][0-9][0-9][0-9]'
OR chardate LIKE '1[0-2]-[2-3][0-9]-[1-2][0-9][0-9][0-9]'
OR chardate LIKE '0[0-9]-1[3-9]-[1-2][0-9][0-9][0-9]'
OR chardate LIKE '1[0-2]-1[3-9]-[1-2][0-9][0-9][0-9]'
THEN 'Possible MM-DD-YYYY'
WHEN chardate LIKE '[2-3][0-9]-0[0-9]-[1-2][0-9][0-9][0-9]'
OR chardate LIKE '[2-3][0-9]-1[0-2]-[1-2][0-9][0-9][0-9]'
OR chardate LIKE '1[3-9]-0[0-9]-[1-2][0-9][0-9][0-9]'
OR chardate LIKE '1[3-9]-1[0-2]-[1-2][0-9][0-9][0-9]'
THEN 'Possible DD-MM-YYYY'
WHEN chardate LIKE '[1-2][0-9][0-9][0-9]-[2-3][0-9]-0[0-9]'
OR chardate LIKE '[1-2][0-9][0-9][0-9]-[2-3][0-9]-1[0-2]'
OR chardate LIKE '[1-2][0-9][0-9][0-9]-1[3-9]-0[0-9]'
OR chardate LIKE '[1-2][0-9][0-9][0-9]-1[3-9]-1[0-2]'
THEN 'Possible YYYY-DD-MM'
WHEN chardate LIKE '[1-2][0-9][0-9][0-9]-0[0-9]-[2-3][0-9]'
OR chardate LIKE '[1-2][0-9][0-9][0-9]-1[0-2]-[2-3][0-9]'
OR chardate LIKE '[1-2][0-9][0-9][0-9]-0[0-9]-1[3-9]'
OR chardate LIKE '[1-2][0-9][0-9][0-9]-1[0-2]-1[3-9]'
THEN 'Possible YYYY-MM-DD'
ELSE 'Unknown' END
GO
DROP TABLE #SampleDates
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply