468,140 Members | 1,463 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,140 developers. It's quick & easy.

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

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 :)
Aug 13 '05 #1
7 60106
Vinnie Davidson wrote:
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/...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"
Aug 13 '05 #2
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 GetProjectsByDate
@D DATETIME
AS
SELECT Title
FROM tblProject
WHERE DeadlineDate BETWEEN @D AND DATEADD(d,1,@D)
GO

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

If you insist on dynamic SQL, you can do something like this:
Today = Date
Tomorrow = DateAdd("d",1,Today)
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.
Aug 13 '05 #3
try this:

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


"Vinnie Davidson" <ad***@webressurs.no> wrote in message
news:OJ**************@TK2MSFTNGP09.phx.gbl...
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 :)

Aug 14 '05 #4
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
.....
%>
"Vinnie Davidson" <ad***@webressurs.no> wrote in message
news:OJ**************@TK2MSFTNGP09.phx.gbl...
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 :)

Aug 14 '05 #5
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(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

--
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"
Aug 14 '05 #6
> 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
Aug 15 '05 #7
> 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.
Aug 15 '05 #8

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

5 posts views Thread by Darrel | last post: by
6 posts views Thread by darrel | last post: by
3 posts views Thread by noone | last post: by
2 posts views Thread by AKorsakova | last post: by
4 posts views Thread by Simon Gare | last post: by
1 post views Thread by gcdp | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.