We had situation where we had an application that feed a varchar date into a column that was not usable to our BI report writers. The column had 3 months letters and a space and the year which was numeric.
To create the sample Table:
SELECT * INTO [Table] FROM ( SELECT N'Apr 2014' AS [Analysis_Date] UNION ALL SELECT N'Feb 2012' AS [Analysis_Date] UNION ALL SELECT N'Jan 2014' AS [Analysis_Date] UNION ALL SELECT N'Jul 2014' AS [Analysis_Date] UNION ALL SELECT N'Jun 2014' AS [Analysis_Date] UNION ALL SELECT N'Mar 2014' AS [Analysis_Date] UNION ALL SELECT N'May 2014' AS [Analysis_Date] UNION ALL SELECT N'Apr 2013' AS [Analysis_Date] ) t; SELECT [Analysis_Date] FROM [Table] --DROP TABLE [Table] GO
First I broke the data into two columns as you can see below with a SUBSTRING.
SELECT DISTINCT DDA_Hist.Analysis_Date , SUBSTRING(DDA_Hist.Analysis_Date, 5, 9) AS [Analysis Year] , LEFT(DDA_Hist.Analysis_Date, 4) AS [Analysis Month] FROM Table
This produces:
Then I took the two substrings and mashed them to recreate the beginning of the month datetime. The magic happens with the SUBSTRING and CHARINDEX.
‘XXJANFEBMARAPRMAYJUNJULAUGSEPOCTNOVDEC’
this string lets me count the months and get a number instead of the months first three letters.
SELECT DISTINCT DDA_Hist.Analysis_Date , SUBSTRING(DDA_Hist.Analysis_Date, 5, 9) AS [Analysis Year] , LEFT(DDA_Hist.Analysis_Date, 4) AS [Analysis Month], CAST(CAST(SUBSTRING(DDA_Hist.Analysis_Date, 5, 9) AS VARCHAR(4)) + RIGHT('0' + CAST(CHARINDEX(LEFT(DDA_Hist.Analysis_Date, 3), 'XXJANFEBMARAPRMAYJUNJULAUGSEPOCTNOVDEC') / 3 AS VARCHAR(2)), 2) + RIGHT('0' + CAST('01' AS VARCHAR(2)), 2) AS DATETIME) AS Analysis_Datetime_BeginMonth FROM Table
This produces:
Taking that same logic we can also get the end of that month datetime as well.
SELECT DISTINCT DDA_Hist.Analysis_Date , SUBSTRING(DDA_Hist.Analysis_Date, 5, 9) AS [Analysis Year] , LEFT(DDA_Hist.Analysis_Date, 4) AS [Analysis Month], CAST(CAST(SUBSTRING(DDA_Hist.Analysis_Date, 5, 9) AS VARCHAR(4)) + RIGHT('0' + CAST(CHARINDEX(LEFT(DDA_Hist.Analysis_Date, 3), 'XXJANFEBMARAPRMAYJUNJULAUGSEPOCTNOVDEC') / 3 AS VARCHAR(2)), 2) + RIGHT('0' + CAST('01' AS VARCHAR(2)), 2) AS DATETIME) AS Analysis_Datetime_BeginMonth</pre> <pre> DATEADD(DAY, -DAY(DATEADD(MONTH, 1, CAST(CAST(SUBSTRING(DDA_Hist.Analysis_Date, 5, 9) AS VARCHAR(4)) + RIGHT('0' + CAST(CHARINDEX(LEFT(DDA_Hist.Analysis_Date, 3), 'XXJANFEBMARAPRMAYJUNJULAUGSEPOCTNOVDEC') / 3 AS VARCHAR(2)), 2) + RIGHT('0' + CAST('01' AS VARCHAR(2)), 2) AS DATETIME))), DATEADD(MONTH, 1, CAST(CAST(SUBSTRING(DDA_Hist.Analysis_Date, 5, 9) AS VARCHAR(4)) + RIGHT('0' + CAST(CHARINDEX(LEFT(DDA_Hist.Analysis_Date, 3), 'XXJANFEBMARAPRMAYJUNJULAUGSEPOCTNOVDEC') / 3 AS VARCHAR(2)), 2) + RIGHT('0' + CAST('01' AS VARCHAR(2)), 2) AS DATETIME))) AS Analysis_Datetime_MonthEnd FROM Table
This produces:
Have Fun!
Advertisements