Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > ASP General > ASP Date: get records with date = today (SQL Server)

Reply
Thread Tools

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

 
 
Vinnie Davidson
Guest
Posts: n/a
 
      08-13-2005
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


 
Reply With Quote
 
 
 
 
Bob Barrows [MVP]
Guest
Posts: n/a
 
      08-13-2005
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"


 
Reply With Quote
 
 
 
 
Michael
Guest
Posts: n/a
 
      08-14-2005
try this:

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




"Vinnie Davidson" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>
>



 
Reply With Quote
 
Michael
Guest
Posts: n/a
 
      08-14-2005
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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>
>



 
Reply With Quote
 
Bob Barrows [MVP]
Guest
Posts: n/a
 
      08-14-2005
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"


 
Reply With Quote
 
Aaron Bertrand [SQL Server MVP]
Guest
Posts: n/a
 
      08-15-2005
> 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


 
Reply With Quote
 
Aaron Bertrand [SQL Server MVP]
Guest
Posts: n/a
 
      08-15-2005
> 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.


 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Notice the date today? What happened 40 years ago today? richard Computer Support 10 07-29-2009 08:20 PM
Input Date Is Between 2 DB Date Records rn5a@rediffmail.com ASP General 2 05-14-2007 01:57 PM
Simple query returns 0 records in asp, but all records in vbscript masg0013@gmail.com ASP General 3 11-02-2006 09:23 AM
Find records with date field before today !TG ASP General 16 07-26-2005 09:23 PM
Date, date date date.... Peter Grison Java 10 05-30-2004 01:20 PM



Advertisments