Search code examples
t-sqlstored-proceduresdate-conversion

How to extract month from a String representing a date in TSQL

In my SQL Server database I have the dates stored as char(12) in the following format:

yyyymmddhhmm

I didn't create the definition and I cannot modify it. I always dealt with this string at application level, by using C#.

Now I need to perform a task in TSQL and I need to group by month. Therefore I need to extract the month. Is there any TSQL function available for this task?

In case there is not, is it a good solution to create a stored procedure, getMonth(stringDate) that takes the string and extract the 4th and 5th characters from it? Can I use the clause:

group by getMonth(stringDate)

in my query? Thanks

Solution

  • You can use the following to get your month, since month is always 2 characters after the 4 character year. 

    declare @date char(12)
set @date = '201203220906'
select substring(@date, 5, 2)

    results: 03

    or another way to get it is, then you can group by the results in either query:

    declare @date char(12)
set @date = '201203220906'
select Month(cast(left(@date, 8) as datetime))