Convert YYYYMM Integer Date to Datetime

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Script Library

Convert YYYYMM Integer Date to Datetime

Author

Topic

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2009-10-16 : 12:11:40

I see a lot of questions posted about this type of conversion, so I thought I would post a general solution to find the first day of the month, the last day of the month, and the first day of the next month (for range queries) from an integer date in the form of YYYYMM.

The YYYYMM conversion is checked by a case statement to verify that the YYYYMM values can be converted to a valid datetime value for that column. The test script includes invalid YYYYMM values to verify these checks.

/*

Convert YYYYMM Integer Date to Datetime

Convert integer dates in the format YYYYMM to
datetime values for first and last day of the month,
and the first day of the next month.

Returns NULL if YYYY part is not between 1753 and 9999, or
MM part is not between 1 and 12, or if YYYYMM is negative or null.
Returns NULL for FirstDayOfNextMonth if YYYYMM >= 999912.

*/

select
	YYYYMM,
	FirstDayOfMonth =
	case
	when 	YYYYMM between 175301 and 999912 and
		YYYYMM%100 between 1 and 12
	then	dateadd(mm,(((YYYYMM/100)-1900)*12)+(YYYYMM%100)-1,0)
	end,
	LastDayOfMonth =
	case
	when 	YYYYMM between 175301 and 999912 and
		YYYYMM%100 between 1 and 12
	then	dateadd(mm,(((YYYYMM/100)-1900)*12)+(YYYYMM%100),-1)
	end,
	FirstDayOfNextMonth =
	case
	when 	YYYYMM between 175301 and 999911 and
		YYYYMM%100 between 1 and 12
	then	dateadd(mm,(((YYYYMM/100)-1900)*12)+(YYYYMM%100),0)
	end
from
	( -- Test Data
	select YYYYMM = 175201 union all
	select YYYYMM = 175301 union all
	select YYYYMM = NULL union all
	select YYYYMM = -200901 union all
	select YYYYMM = 200900 union all
	select YYYYMM = 200901 union all
	select YYYYMM = 200901 union all
	select YYYYMM = 200902 union all
	select YYYYMM = 200903 union all
	select YYYYMM = 200904 union all
	select YYYYMM = 200905 union all
	select YYYYMM = 200906 union all
	select YYYYMM = 200907 union all
	select YYYYMM = 200908 union all
	select YYYYMM = 200909 union all
	select YYYYMM = 200910 union all
	select YYYYMM = 200911 union all
	select YYYYMM = 200912 union all
	select YYYYMM = 200913 union all
	select YYYYMM = 999912 union all
	select YYYYMM = 1000001
	) a

Test Script Results:

YYYYYMM      FirstDayOfMonth          LastDayOfMonth           FirstDayOfNextMonth
-----------  -----------------------  -----------------------  -----------------------
175201       NULL                     NULL                     NULL
175301       1753-01-01 00:00:00.000  1753-01-31 00:00:00.000  1753-02-01 00:00:00.000
NULL         NULL                     NULL                     NULL
-200901      NULL                     NULL                     NULL
200900       NULL                     NULL                     NULL
200901       2009-01-01 00:00:00.000  2009-01-31 00:00:00.000  2009-02-01 00:00:00.000
200901       2009-01-01 00:00:00.000  2009-01-31 00:00:00.000  2009-02-01 00:00:00.000
200902       2009-02-01 00:00:00.000  2009-02-28 00:00:00.000  2009-03-01 00:00:00.000
200903       2009-03-01 00:00:00.000  2009-03-31 00:00:00.000  2009-04-01 00:00:00.000
200904       2009-04-01 00:00:00.000  2009-04-30 00:00:00.000  2009-05-01 00:00:00.000
200905       2009-05-01 00:00:00.000  2009-05-31 00:00:00.000  2009-06-01 00:00:00.000
200906       2009-06-01 00:00:00.000  2009-06-30 00:00:00.000  2009-07-01 00:00:00.000
200907       2009-07-01 00:00:00.000  2009-07-31 00:00:00.000  2009-08-01 00:00:00.000
200908       2009-08-01 00:00:00.000  2009-08-31 00:00:00.000  2009-09-01 00:00:00.000
200909       2009-09-01 00:00:00.000  2009-09-30 00:00:00.000  2009-10-01 00:00:00.000
200910       2009-10-01 00:00:00.000  2009-10-31 00:00:00.000  2009-11-01 00:00:00.000
200911       2009-11-01 00:00:00.000  2009-11-30 00:00:00.000  2009-12-01 00:00:00.000
200912       2009-12-01 00:00:00.000  2009-12-31 00:00:00.000  2010-01-01 00:00:00.000
200913       NULL                     NULL                     NULL
999912       9999-12-01 00:00:00.000  9999-12-31 00:00:00.000  NULL
1000001      NULL                     NULL                     NULL

(21 row(s) affected)

Of course, if the data is so clean that the checks are unnecessary (

), they could be removed and just the conversion in the “then” part of the case would be needed:

select
	YYYYMM,
	FirstDayOfMonth     = dateadd(mm,(((YYYYMM/100)-1900)*12)+(YYYYMM%100)-1,0),
	LastDayOfMonth      = dateadd(mm,(((YYYYMM/100)-1900)*12)+(YYYYMM%100),-1),
	FirstDayOfNextMonth = dateadd(mm,(((YYYYMM/100)-1900)*12)+(YYYYMM%100),0)
from
	( -- Test Data
	select YYYYMM = 175301 union all
	select YYYYMM = 200802 union all
	select YYYYMM = 200901 union all
	select YYYYMM = 200901 union all
	select YYYYMM = 200902 union all
	select YYYYMM = 200903 union all
	select YYYYMM = 200904 union all
	select YYYYMM = 200905 union all
	select YYYYMM = 200906 union all
	select YYYYMM = 200907 union all
	select YYYYMM = 200908 union all
	select YYYYMM = 200909 union all
	select YYYYMM = 200910 union all
	select YYYYMM = 200911 union all
	select YYYYMM = 200912 union all
	select YYYYMM = 999911
	) a

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources