Setting ToDate parameter

  • 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.

  • 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
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

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

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply