Mark a Date as the Start or End of Week, Month, Quarter and Year



create table MyDates(MyDate  date, MyFlag  Varchar(10) )
insert MyDates(MyDate) Values
('2014-01-01'),   -- ys
('2014-01-31'),   -- me
('2014-04-01'),   -- ms, qs
('2014-03-31'),   -- me, qe
('2014-01-06'),   -- ws
('2014-01-12'),   -- we
('2014-12-31'),  -- ye
('2014-01-04');  -- nothing special, so NULL


SELECT MyDate, Reverse(Stuff(Reverse(
 Case WHen Datepart(weekday, MyDate)=2 Then'ws,' 
 WHEN Datepart(weekday, MyDate)=1 Then'we,' Else'' End
  + 
 CASE When Datediff(day,DATEADD(mm, DATEDIFF(mm, 0, MyDate) , 0), MyDate )=0 Then'ms,' 
 When Datediff(day,DATEADD(mm, DATEDIFF(mm, 0, MyDate)+1 , 0), cast(MyDate as datetime)+1 )=0 Then'me,'
 Else'' End
 + 
 CASE When Datediff(day,DATEADD(Q, DATEDIFF(Q, 0, MyDate) , 0), MyDate )=0 Then'qs,' 
 When Datediff(day,DATEADD(Q, DATEDIFF(Q, 0, MyDate)+1 , 0), cast(MyDate as datetime)+1 )=0 Then'qe,' Else'' End 
   +
 Case When Datediff(day, DATEADD(yy, DATEDIFF(yy,0,MyDate),0), MyDate )=0 Then'ys,'
  When Datediff(day, DATEADD(yy, DATEDIFF(yy,0,MyDate)+1,0),  cast(MyDate as datetime)+1 )=0 Then'ye,' Else'' End  
 ),1,1,'')) as MyFlag 
  
FROM  MyDates
 
Drop Table MyDates;


 



 
Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s