6 Comments

1. Ramon says:

   November 21, 2011 at 12:15 am

   Wow Greate tutorial…. Thanx

   Reply to this comment
2. rajani says:

   May 23, 2011 at 9:54 am

   Thank You!!
   I stumbled accross this today and it helped fix up my code perfectly.

   Reply to this comment
3. Harvey says:

   February 2, 2011 at 1:11 pm

   Dereck…Nevermind. I figured out what needed to be done. Thanks anyway!

   -Harvey

   Reply to this comment
4. Harvey says:

   February 2, 2011 at 12:16 pm

   Why it keeps gettign cut off…I don’t know. But here is another attempt at pasting it in;

   CASE WHEN DATEPART(mm, GetDate()) = 1 then
   (DatePart(dd, pl.Plcmt_From_Dt) >= 11
   and DatePart(mm, pl.Plcmt_From_Dt) = 12
   and DatePart(Year, pl.Plcmt_From_Dt) = DatePart(Year, GetDate())-1
   OR
   DatePart(dd, pl.Plcmt_From_Dt) = 11 AND (DATEPART(mm, pl.Plcmt_From_Dt) = DATEPART(mm, GETDATE())-1
   and DATEPART(Year, pl.Plcmt_From_Dt) = DATEPART(Year, GETDATE()))
   OR
   DATEPART(dd, pl.Plcmt_From_Dt) <= 10 AND DATEPART(mm, pl.Plcmt_From_Dt) = DATEPART(mm, GETDATE()) AND DATEPART(Year, pl.Plcmt_From_Dt) = DATEPART(Year, GETDATE())
   end

   Reply to this comment
5. Harvey says:

   February 2, 2011 at 12:09 pm

   Dereck,

   I just subscribed to your newsletter and commented after using one of your posts to use the correct syntax for CAST functions. I have a question and am hoping that you can help me. I am writing a query where I am attempting to use a CASE expression in the WHERE clause using DATEPART. I am trying to build it so that if the month that a report is being run is January then to return records that are from the 11th of December of last year through the 10th of January of the current year ELSE return the records for the same date range of the current year. The code that I have is as follows;

   CASE WHEN DATEPART(mm, GetDate()) = 1 then
   (DatePart(dd, pl.Plcmt_From_Dt) >= 11
   and DatePart(mm, pl.Plcmt_From_Dt) = 12
   and DatePart(Year, pl.Plcmt_From_Dt) = DatePart(Year, GetDate())-1
   OR
   DatePart(dd, pl.Plcmt_From_Dt) = 11 AND (DATEPART(mm, pl.Plcmt_From_Dt) = DATEPART(mm, GETDATE())-1
   and DATEPART(Year, pl.Plcmt_From_Dt) = DATEPART(Year, GETDATE()))
   OR
   DATEPART(dd, pl.Plcmt_From_Dt) ‘.

   Any help you can offer would be greatly appreciated!

   Sincerely,
   Harvey

   Reply to this comment
6. Eden says:

   February 1, 2010 at 5:15 pm

   Thank you so much for this posting. I used it in a query at work and it fit the bill 100%. Saved me a bunch of work

   Reply to this comment