• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 171
  • Last Modified:

ASP using date as where condition

Hey Guys,

I am trying to return records in a standard ASP page where the date meets a certain criteria. I am able to do this in the query manager in SQL Server 2005 Express fine whiich is where the database is using the following statement

Select * from tblCallHIstory Where calldatetime > dateadd(day, datediff(day,0,getdate()),0)-4

but when I convert this to use it on an asp page using the following it returns all records.

Select * from tblCallHIstory Where calldatetime > dateadd("d", datediff("d",0,now()),0)-4

Can anyone see where the flaw is in my logic?

Regards,

Nigel
0
Nigel_Taylor
Asked:
Nigel_Taylor
  • 4
  • 2
1 Solution
 
hongjunCommented:
You can also use the first sql statement.
No problem with that.

strSql = "Select * from tblCallHIstory Where calldatetime > dateadd(day, datediff(day,0,getdate()),0)-4"

0
 
hongjunCommented:
If you want to use your second statement, try this

strSql = "Select * from tblCallHIstory Where calldatetime > '" & dateadd("d", datediff("d",0,now()),0)-4 & "'"
0
 
Nigel_TaylorAuthor Commented:
If I use the first statement as is then it does not like the parameters but change them to make them ASP compliant then it just returns all records.

I have tried your updated version with the added quotes and I am getting

The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

Regards,

Nigel
0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
hongjunCommented:
try this then

strSql = "Select * from tblCallHIstory Where calldatetime > getdate()-4"

Or

strSql = "Select * from tblCallHIstory Where calldatetime > '" & Now()-4 & "'"
0
 
Nigel_TaylorAuthor Commented:
Excellent we are getting somewhere. The only thing is its picked up all records after 13:00 as I take it getdate() is returning time so how do I set the time part to 00:00:00?

Regards,

Nigel
0
 
hongjunCommented:
Try this

strSql = "Select * from tblCallHIstory Where calldatetime > cast(convert(varchar, getdate()-4, 112) as datetime)"
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now