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

Discussion in 'ASP General' started by Vinnie Davidson, Aug 13, 2005.

  1. 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 :)
     
    Vinnie Davidson, Aug 13, 2005
    #1
    1. Advertising

  2. 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/microsoft.public.inetserver.asp.db/msg/72e36562fee7804e

    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"
     
    Bob Barrows [MVP], Aug 13, 2005
    #2
    1. Advertising

  3. Vinnie Davidson

    Michael Guest

    try this:

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




    "Vinnie Davidson" <> wrote in message
    news:...
    > 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 :)
    >
    >
     
    Michael, Aug 14, 2005
    #3
  4. Vinnie Davidson

    Michael Guest

    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" <> wrote in message
    news:...
    > 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 :)
    >
    >
     
    Michael, Aug 14, 2005
    #4
  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"
     
    Bob Barrows [MVP], Aug 14, 2005
    #5
  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
     
    Aaron Bertrand [SQL Server MVP], Aug 15, 2005
    #6
  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.
     
    Aaron Bertrand [SQL Server MVP], Aug 15, 2005
    #7
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. TB
    Replies:
    0
    Views:
    464
  2. Phil Powell
    Replies:
    2
    Views:
    159
    Julian Roberts
    Feb 17, 2004
  3. !TG
    Replies:
    16
    Views:
    319
  4. Replies:
    3
    Views:
    680
    Anthony Jones
    Nov 2, 2006
  5. joeyej
    Replies:
    7
    Views:
    158
    Randy Webb
    Apr 6, 2006
Loading...

Share This Page