Get MonthDay from date - SQL Server Forums

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

New to SQL Server Programming

Get MonthDay from date

Author

Topic

valedaw2
Yak Posting Veteran

50 Posts

Posted - 2009-08-21 : 09:10:02

I need to extract the Month and Date from a date field. I am setting a quarter date based a date range. i.e. If the Date is between 2/16/YYYY and 5/15/yyyy, use 4/1/yyyy. If the Date is between 5/16/YYYY and 8/15/yyyy, use 7/1/yyyy.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-08-21 : 10:25:36

Well, it's kinda ugly, but it works:

select dateadd(quarter, datediff(quarter, 0, dateadd(day, case when month(dateCol) between 3 and 5 then -45 else -46 end, dateCol))+1, 0)
from myTable

valedaw2
Yak Posting Veteran

50 Posts

Posted - 2009-08-21 : 10:48:22

Wow! Thanks so much. It does work. Do you think you can explain what it is doing? I want to understand the logic.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-08-21 : 11:28:53

The DatePart function can extract any date interval, in your case the quarter. The DateAdd math shifts your dates to match the quarter boundaries. The CASE expression handles the short month of February.

valedaw2
Yak Posting Veteran

50 Posts

Posted - 2009-08-21 : 11:43:07

Once again, thanks

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-08-21 : 11:47:29

You're welcome.

I'm a dummy, I said "Datepart" before, I meant DateDiff.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2009-08-21 : 11:49:50

Here is another method:

select
	a.DT,
	QuarterDate =
	dateadd(qq,datediff(qq,0,dateadd(mm,2,dateadd(dd,-15,a.DT))),0)
from
	(
	select DT = convert(datetime,'2009-02-15') union all
	select DT = convert(datetime,'2009-02-16') union all
	select DT = convert(datetime,'2009-05-15') union all
	select DT = convert(datetime,'2009-05-16') union all
	select DT = convert(datetime,'2009-08-15') union all
	select DT = convert(datetime,'2009-08-16') union all
	select DT = convert(datetime,'2009-11-15') union all
	select DT = convert(datetime,'2009-11-16') union all
	select DT = convert(datetime,'2010-02-15') union all
	select DT = convert(datetime,'2010-02-16')
	) a

Results:

DT                       QuarterDate
-----------------------  -----------------------
2009-02-15 00:00:00.000  2009-01-01 00:00:00.000
2009-02-16 00:00:00.000  2009-04-01 00:00:00.000
2009-05-15 00:00:00.000  2009-04-01 00:00:00.000
2009-05-16 00:00:00.000  2009-07-01 00:00:00.000
2009-08-15 00:00:00.000  2009-07-01 00:00:00.000
2009-08-16 00:00:00.000  2009-10-01 00:00:00.000
2009-11-15 00:00:00.000  2009-10-01 00:00:00.000
2009-11-16 00:00:00.000  2010-01-01 00:00:00.000
2010-02-15 00:00:00.000  2010-01-01 00:00:00.000
2010-02-16 00:00:00.000  2010-04-01 00:00:00.000

(10 row(s) affected)

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources