ASP Date: get records with date = today (SQL Server)

[Vinnie Davidson]

Hello!

I'm trying to get all records from my SQL Server Database with
"DeadlineDate" = today (not today - 24 hours).

All records has a field called "DeadlineDate", and the date is stored in
this field like this: 13.08.2005 07:00:00

I dont care about the hours (Ex: 07:00:00), just the date (ex: 13.08.2005).
This is the SQL I have made, it gets all the record with the date = today -
24 hours... but that is not what I want.

sql = "select title from tblProject where (deadlineDate BETWEEN DATEADD(d, -
1, GETDATE()) AND GETDATE())"

How can I get just the records that has the date = today's date??


Thanks for all tips

 

[Bob Barrows [MVP]]

Hello!

I'm trying to get all records from my SQL Server Database with
"DeadlineDate" = today (not today - 24 hours).

All records has a field called "DeadlineDate", and the date is stored
in this field like this: 13.08.2005 07:00:00

I dont care about the hours (Ex: 07:00:00), just the date (ex:
13.08.2005). This is the SQL I have made, it gets all the record with
the date = today - 24 hours... but that is not what I want.

This expression strips the time from a date (I'll use GETDATE() to supply
the date in this example, but any datetime variable could be used):

dateadd(day,datediff(day,0,GETDATE() )­, 0)

Where DeadlineDate >= dateadd(day,datediff(day,0,GETDATE() )­, 0)
AND DeadlineDate <
dateadd(day,1,dateadd(day,datediff(day,0,GETDATE() )­, 0) )

It looks as if you are using dynamic sql which can leave your site
vulnerable to hackers using sql injection:
http://mvp.unixwiz.net/techtips/sql-injection.html
http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23

You can prevent sql injection by using parameters, either via stored
procedures:
http://tinyurl.com/jyy0

or by using a Command object to pass parameters to a string containing ODBC
parameter markers:
http://groups-beta.google.com/group/microsoft.public.inetserver.asp.db/msg/72e36562fee7804e

HTH,
Bob Barrows

 

[Michael]

try this:

select cast(cast(getdate() as integer) as datetime)

 

[Michael]

or for your case, can try this code in your asp page
<%
strDate=right("00" & month(now()),2) & "/" & right("00" & day(now()),2) &
"/" & year(now())

strSQL="select * from table_name where cast(cast(DeadlineDate as integer) as
datetime)='" & strDate & "'"

objConnection.execute strSQL
.....


%>

 

[Bob Barrows [MVP]]

or for your case, can try this code in your asp page
<%
strDate=right("00" & month(now()),2) & "/" & right("00" &
day(now()),2) & "/" & year(now())

strSQL="select * from table_name where cast(cast(DeadlineDate as
integer) as datetime)='" & strDate & "'"

objConnection.execute strSQL
....

You may find that this "works", but, if you have an index on DeadlineDate,
you will find tat it will not be used, leading your query to perform a table
scan, not exactly the quickest way to extract data from a table ...

Bob Barrows

 

[Aaron Bertrand [SQL Server MVP]]

WHERE DeadlineDate BETWEEN @D AND DATEADD(d,1,@D)

I'd really stay away from BETWEEN here. This says

WHERE col BETWEEN '20050815 0:00' AND '20050816 0:00'

The nature of the data might be such that many rows are inserted for the
16th with no time associated, so many rows for the 16th will come back with
data for the 16th.

I would much prefer

WHERE col >= @D AND col < (@D + 1)

The back side of the range should not include the end point because it's a
different day. I wrote about this here:
http://www.aspfaq.com/2280

A

 

[Aaron Bertrand [SQL Server MVP]]

Set oRS = oCN.Execute("GetProjectsByDate '" & Date & "'")

And the problem with this, as opposed to letting SQL Server figure out what
today is, is that your web server and SQL Server better be in sync, or else
you could get wrong data or an error, e.g. if VBScript gives you dd/mm/yyyy
and SQL Server is expecting mm/dd/yyyy.