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
"DeadlineDa te" = today (not today - 24 hours).

All records has a field called "DeadlineDa te", 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]]

Vinnie Davidson wrote:

Hello!

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

All records has a field called "DeadlineDa te", 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,dat ediff(day,0,GET DATE() )*, 0)

Where DeadlineDate >= dateadd(day,dat ediff(day,0,GET DATE() )*, 0)
AND DeadlineDate <
dateadd(day,1,d ateadd(day,date diff(day,0,GETD ATE() )*, 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/...e36562fee7804e

HTH,
Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[Dave Anderson]

Vinnie Davidson wrote:

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??

For starters, you can use VBScript's [Date], which contains no time info:
http://msdn.microsoft.com/library/en.../vsfctdate.asp

The rest is simple, especially if you are using a stored procedure, such as:

CREATE PROCEDURE GetProjectsByDa te
@D DATETIME
AS
SELECT Title
FROM tblProject
WHERE DeadlineDate BETWEEN @D AND DATEADD(d,1,@D)
GO

VBScript usage:
Set oRS = oCN.Execute("Ge tProjectsByDate '" & Date & "'")

If you insist on dynamic SQL, you can do something like this:
Today = Date
Tomorrow = DateAdd("d",1,T oday)
SQL = "select title from tblProject where deadlineDate " _
"BETWEEN '" & Today & "' AND '" & Tomorrow & "'"

--
Dave Anderson

Unsolicited commercial email will be read at a cost of $500 per message. Use
of this email address implies consent to these terms. Please do not contact
me directly or ask me to contact you directly for assistance. If your
question is worth asking, it's worth posting.

[Michael]

try this:

select cast(cast(getda te() as integer) as datetime)


"Vinnie Davidson" <ad***@webressu rs.no> wrote in message
news:OJ******** ******@TK2MSFTN GP09.phx.gbl...

Hello!

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

All records has a field called "DeadlineDa te", 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 :)

[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(Deadl ineDate as integer) as
datetime)='" & strDate & "'"

objConnection.e xecute strSQL
.....
%>
"Vinnie Davidson" <ad***@webressu rs.no> wrote in message
news:OJ******** ******@TK2MSFTN GP09.phx.gbl...

Hello!

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

All records has a field called "DeadlineDa te", 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]]

Michael wrote:

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(Deadl ineDate as
integer) as datetime)='" & strDate & "'"

objConnection.e xecute 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

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[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("Ge tProjectsByDate '" & 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.