Help => MSSQL : Calculating dates within a query

[laredo512]

Hi all,

Im trying to create a generic SQL query where it would retreive records based on the following:

1. All records have a date field for the entry date.
2. I need to build a query that retrieves records that have been there for more than 29 days.

Any thoughts ?

Thanks

 

[Nerseus]

If this is SQL server Id do something like this:

DECLARE @FilterDate datetime
SET @FilterDate = DATEADD(d, -29, getdate())

SELECT ...
FROM ...
WHERE EntryDate <= @FilterDate

That may need some tweaks, in case EntryDate has a time stored in the column. I may be off on the -29 - you may need to subtract 30 or 28, depending on how you code the "EntryDate <= @FilterDate" - maybe use "<" instead of "<=".

-ner

 

[laredo512]

If this is SQL server Id do something like this:

DECLARE @FilterDate datetime
SET @FilterDate = DATEADD(d, -29, getdate())
 
SELECT ...
FROM ...
WHERE EntryDate <= @FilterDate

That may need some tweaks, in case EntryDate has a time stored in the column. I may be off on the -29 - you may need to subtract 30 or 28, depending on how you code the "EntryDate <= @FilterDate" - maybe use "<" instead of "<=".

-ner

Thanks Nerseus, it is MSSQL Server.
The dates have the time integrated as well.

 

[Nerseus]

When I have columns that have a time along with the date and I need a query to filter by a day, that usually means that:
If filter date = 1/3/2006 then records with a date of 1/3/2006 should be included regardless of time.

I usually use the following approach:
Get your compare variable to have only a date (strip off the time)
Set the compare variable to one day GREATER than what you want to include
Use a "<" check instead of "<="

For your sample, that means using something like:

DECLARE @FilterDate datetime
-- Set the @FilterDate to be 29 days in the past, minus any time portion
-- The varchar convert strips off the time while the outside convert puts it back to date/time
-- The outside convert isnt necessary as SQL will convert the varchar into a datetime automatically
SET @FilterDate = CONVERT(datetime, CONVERT(varchar, DATEADD(d, -29, getdate()), 101))

-- Add an extra day so an "inclusive" check will work easier
SET FilterDate = DATEADD(d, 1, @FilterDate)

SELECT ...
FROM ...
WHERE EntryDate < @FilterDate

You could also shortcut that into the following although I prefer to be verbose in the code to make it more readable 6 months later:

SET @FilterDate = CONVERT(datetime, CONVERT(varchar, DATEADD(d, -29, getdate()), 101))

-- Add an extra day so an "inclusive" check will work easier
SET FilterDate = DATEADD(d, 1, @FilterDate)

SELECT ...
FROM ...
WHERE EntryDate < CONVERT(datetime, CONVERT(varchar, DATEADD(d, -28, @FilterDate), 101))

In the second example I inlined the code and subtracted 28 instead of 29 to save the extra "one more day" check. Here the extra CONVERT back to datetime is necessary - if you dont use it, the SQL analyzer will try to CONVERT the column to a varchar and hurt performance. You can test in query analyzer by using the command "set statistics profile on".

I hate fudging hard-code numbers like 29 for 28 to make the code "leaner". If the business rule says "include records that are 29 days or older" then I want the code to have a "29" somewhere - just makes things easier later on.

-ner

 

[laredo512]

Thanks for this elaborate reply. Its quite helpful

Since were on the Microsoft side of SQL, if I were to host a database server for external users such as:

1 - The users use an application that connects to MS SQL for the DB operations it requires
2 - The server is off site at another office where it also serves as a web server.

I was thinking of getting a Cisco Firewall unit and hard code their fixed IP to let them through... unless theres a way to have them use a secure channel ?

Thanks again Nerseus. Much appreciated

 

[Nerseus]

Id post this as a separate issue, so it can get the attention it deserves. Id mention quickly that Microsoft highly recommends that the SQL Server and Web servers stay separate - and Id take that advice. SQL takes up a lot of memory, disk and CPU. Webservers are cheap, comparatively, and easier to scale out if needed. You normally only want one SQL Server box - but a big one.

-ner

 

[laredo512]

When I have columns that have a time along with the date and I need a query to filter by a day, that usually means that:
If filter date = 1/3/2006 then records with a date of 1/3/2006 should be included regardless of time.

I usually use the following approach:
Get your compare variable to have only a date (strip off the time)
Set the compare variable to one day GREATER than what you want to include
Use a "<" check instead of "<="

For your sample, that means using something like:

DECLARE @FilterDate datetime
-- Set the @FilterDate to be 29 days in the past, minus any time portion
-- The varchar convert strips off the time while the outside convert puts it back to date/time
-- The outside convert isnt necessary as SQL will convert the varchar into a datetime automatically
SET @FilterDate = CONVERT(datetime, CONVERT(varchar, DATEADD(d, -29, getdate()), 101))
 
-- Add an extra day so an "inclusive" check will work easier
SET FilterDate = DATEADD(d, 1, @FilterDate)
 
SELECT ...
FROM ...
WHERE EntryDate < @FilterDate

You could also shortcut that into the following although I prefer to be verbose in the code to make it more readable 6 months later:

SET @FilterDate = CONVERT(datetime, CONVERT(varchar, DATEADD(d, -29, getdate()), 101))
 
-- Add an extra day so an "inclusive" check will work easier
SET FilterDate = DATEADD(d, 1, @FilterDate)
 
SELECT ...
FROM ...
WHERE EntryDate < CONVERT(datetime, CONVERT(varchar, DATEADD(d, -28, @FilterDate), 101))

In the second example I inlined the code and subtracted 28 instead of 29 to save the extra "one more day" check. Here the extra CONVERT back to datetime is necessary - if you dont use it, the SQL analyzer will try to CONVERT the column to a varchar and hurt performance. You can test in query analyzer by using the command "set statistics profile on".

I hate fudging hard-code numbers like 29 for 28 to make the code "leaner". If the business rule says "include records that are 29 days or older" then I want the code to have a "29" somewhere - just makes things easier later on.

-ner

Thanks Nersus, I resulted in using your "verbose" example and it works perfectly. Thank you very much for your help.