Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Setting ToDate parameter Expand / Collapse
Author
Message
Posted Monday, March 18, 2013 10:39 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Today @ 5:06 AM
Points: 18, Visits: 75
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.
Post #1432262
Posted Monday, March 18, 2013 11:37 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 3:37 PM
Points: 4,389, Visits: 9,522
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 opportunites brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs
Post #1432284
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse