Setting ToDate parameter

• justfillingtime

  Right there with Babe

  Points: 714

  More actions

  March 18, 2013 at 10:39 am

  #290996

  Hi All,

  Quick question, I'm looking to set my 'ToDate' parameter to the last day of the month up to 23:59:59 but can't figure it out.

  I've got my parameter to set the the final day of the month (using this: =DateSerial(Year(Now()), Month(Now()), "1").AddDays(-1)) but not up to a second before midnight, so infact it is missing out the final day of the months data.

  Many thanks.

• Jeffrey Williams

  SSC Guru

  Points: 90349

  More actions

  March 18, 2013 at 11:37 am

  #1598125

  You really don't want to be building your searches this way. What happens when you change from a datetime data type to a datetime2 data type?

  You will then be forced to update your code because now it could be missing data again.

  A better way to do your checks is to use this:

  WHERE datecolumn >= {start date and time = 00:00:00.000}

  AND datecolumn < {end date + 1 and time = 00:00:00.000}

  For example:

  WHERE datecolumn >= '2013-01-01 00:00:00.000' -- from the first of the year

  AND datecolumn < '2013-03-01 00:00:00.000' -- less than March 1st

  The above will include everything for January and February - but not include March.

  Using BETWEEN or <= requires you to add the time, and as I stated before will require that you update your code if the data type changes.

  Jeffrey Williams
  “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

  ― Charles R. Swindoll

  How to post questions to get better answers faster
  Managing Transaction Logs