Help => MSSQL : Calculating dates within a query

[laredo512]

Hi all,

 

I'm 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 I'd 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 I'd 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 isn't 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 don't 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. It's quite helpful :)

 

Since we're 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 there's a way to have them use a secure channel ?

 

Thanks again Nerseus. Much appreciated

[Nerseus]

I'd post this as a separate issue, so it can get the attention it deserves. I'd mention quickly that Microsoft highly recommends that the SQL Server and Web servers stay separate - and I'd 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 isn't 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 don't 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.