CODEWITHSUNDEEP
sqlsql-server-2008t-sql
TaroYuki
TaroYuki

Reputation: 147

Format varchar type and convert it to date variable

I have a varchar value in this format “YYYYMM-LL” where: YYYY is four digit year; MM is two digit month.LL is two digit length of reporting period. This is the number of whole months included in the calculations (from first to last day of each month).

My questions is how I could use this varchar value to decide two variables:@First_Date and @Last_Date.

Example: 201409-06 This scenario is for the six months ending in September of 2014. The range of calendar dates in this scenario is 04/01/2014 through 09/30/2014.

So how could I use t-sql to find this two dates @First_Date : 04/01/2014 and @Last_Date: 09/30/2014.

Any help would be really appreciated!

Upvotes: 0

Views: 146

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269483

You can do something like this:

select @firstdate = dateadd(month, 1 - cast(right('201409-06', 2) as int),
                            convert(date, left('201409-06', 6) + '01')
                           ),
       @lastdate = dateadd(day, -1,
                           dateadd(month, 1,
                                   convert(date, left('201409-06', 6) + '01')
                                  )
                          )

This parses the string to do the date arithmetic that you want. SQL Server recognizes a string in the form of "YYYYMMDD" as a date, regardless of internationalization settings.

And a SQL Fiddle.

Upvotes: 2

Related Questions