ASP SQL Server Date Range and DateTime field

Question

I am using Classic ASP and have the following VB script code

From8to14 = date-14
To8to14 = date-8

This sets the two variables.

For today, this would mean that

From8to14 = "13/07/2012"
To8to14 = "19/07/2012"

I then have a select query to SQL server, and I want to find any records where the DateMatched column is between (and including) these two dates

I have two conditions in the SQL when I declare it;

"AND INTRAY.DateMatched >= '"& Year(From8to14) &"-"& Month(From8to14) &"-"& Day(From8to14) &"' " & _
"AND INTRAY.DateMatched <= '"& Year(To8to14) &"-"& Month(To8to14) &"-"& Day(To8to14) &"' " & _

Now, this is not working properly because DateMatched column also includes a time in addition to the date, and I think the server is interepreting these dates as being at midnight at the start of that day ?

So, if a record has a DateMatched of "19/07/2012 17:41:22" then it is not being included.

How can I get around this?

Mikael Eriksson · Accepted Answer · 2012-07-27 12:16:16Z

1

Try this.

"AND INTRAY.DateMatched >= dateadd(day, datediff(day, 0, getdate())-14, 0)" & _
"AND INTRAY.DateMatched < dateadd(day, datediff(day, 0, getdate())-7, 0)" & _

answered Jul 27, 2012 at 12:16

Mikael Eriksson

139k22 gold badges223 silver badges293 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

AnthonyWJones Over a year ago

+1 This one gets my vote, it smells better. SQL composition through string concatenation should be minimised and challenged when used.

podiluska · Accepted Answer · 2012-07-27 12:11:18Z

1

Change

To8to14 = date-8

to

To8to14 = date-7

Then you'll go from midnight on the -14th day to midnight on the -7th day, which will include all the -8th day.

Or if you want to be really precise, leave To8to14 as it is, and change the end to

... Day(To8to14) &" 23:59:59'

You could also use BETWEEN rather than >= and <=

answered Jul 27, 2012 at 12:11

podiluska

51.6k7 gold badges102 silver badges105 bronze badges

1 Comment

Andomar Over a year ago

+1 But he should use >= and < instead of between; the end date is exclusive

Collectives™ on Stack Overflow

ASP SQL Server Date Range and DateTime field

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related