Date manipulation and CASE statements

• mattech06

  SSCrazy

  Points: 2484

  More actions

  March 1, 2015 at 12:53 am

  #298231

  Hi,

  I have a question on date manipulation functions and CASE statements

  My sql is passed the following parameter's and performs a select using a manipulation on these date param's to get a start and end date range depending on the conditions;-

  monthColHeader = eg 'Feb 2015'

  defaultStartDate and defaultEndDate

  filterStartDate and filterEndDate.

  These are my conditions;-

  if defaultStart and End = filterStart and End use monthColHeader for the date range

  if defaultStart and End != filetrStart and End AND the month/year of filterStart and filterEnd match then use the filterStart & End month/Year with the monthColHeader to get the date range

  if defaultStart and End != filetrStart and End AND the month/year of filterStart and filterEnd DON't match use filterStart Day and monthColHeader for our start date and monthColHeader for our end date.

  When I say use monthColHeader I mean like this;-

  (r.dbAddDate >= (CAST('@Request.monthColHeader ~' AS DATETIME)) AND r.dbAddDate < DATEADD(mm,1,'@Request.monthColHeader ~'))

  This sql works for converting say 'Feb 2015' to '2015-02-01' & '2015-02-28'

  but it's the rest of the sql needed I'm unclear on.

  thanks,

• ScottPletcher

  SSC Guru

  Points: 101120

  More actions

  March 2, 2015 at 11:22 am

  #1781180

  To keep the optimizer's task straightforward, I would set variables to the desired and end date, patterning the code like this:

  --set local variables for start and end date

  DECLARE @start_date datetime

  DECLARE @end_date datetime

  SELECT

  @start_date = CASE WHEN defaultStartDate = filterStateDate AND defaultEndDate = filterEndDate

  THEN CAST(monthColHeader~ AS datetime)

  WHEN DATEDIFF(MONTH, filterStartDate, filterEndDate) = 0

  THEN filterStartDate --?

  ELSE --?

  --main query to retrieve data

  SELECT ...

  FROM ... AS r

  WHERE r.dbAddDate >= @start_date AND r.dbAddDate < @end_date

  But I can't finish the code because I don't know what you mean by:

  use the filterStart & End month/Year with the monthColHeader ... use filterStart Day and monthColHeader for our start date

  Huh? How do you want to combine the monthColHeader and the other dates?

  SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".