time & n date query

Discussion in 'ASP General' started by dave, May 3, 2004.

  1. dave

    dave Guest

    hi guys
    i m trying to fetch current week's (i.e. today + 6 days
    more ) records using sql

    sql3="Select * From tblEndMinTerm WHERE DateEnd >= GetDate
    () AND DateEnd <= DATEADD(day,6,GetDate()) Order By
    DateEnd Desc"

    but it doesnt display todays record however it display
    data from tomorro's date...

    i think its problem with time field ..i need to specify
    from 00:00:00 to 23:59:59 with start date and end date..

    can any one help me..
    thanx..
    dave
    dave, May 3, 2004
    #1
    1. Advertising

  2. dave

    Ken Schaefer Guest

    WHERE DateEnd >= CAST(CONVERT(GetDate(), varChar, 112) AS SmallDateTime)

    etc

    Converting to varchar in the specified format, drops the hh:mm:ss, and then
    you convert it back to smallDateTime

    Cheers
    Ken


    "dave" <> wrote in message
    news:746301c430c6$49ae6400$...
    : hi guys
    : i m trying to fetch current week's (i.e. today + 6 days
    : more ) records using sql
    :
    : sql3="Select * From tblEndMinTerm WHERE DateEnd >= GetDate
    : () AND DateEnd <= DATEADD(day,6,GetDate()) Order By
    : DateEnd Desc"
    :
    : but it doesnt display todays record however it display
    : data from tomorro's date...
    :
    : i think its problem with time field ..i need to specify
    : from 00:00:00 to 23:59:59 with start date and end date..
    :
    : can any one help me..
    : thanx..
    : dave
    Ken Schaefer, May 3, 2004
    #2
    1. Advertising

  3. dave

    dave Guest

    no not working...
    can u suggest me how to put 00:00:00 and 23:59:59 between
    start and end date???

    thanx
    dave
    >-----Original Message-----
    >WHERE DateEnd >= CAST(CONVERT(GetDate(), varChar, 112)

    AS SmallDateTime)
    >
    >etc
    >
    >Converting to varchar in the specified format, drops the

    hh:mm:ss, and then
    >you convert it back to smallDateTime
    >
    >Cheers
    >Ken
    >
    >
    >"dave" <> wrote in

    message
    >news:746301c430c6$49ae6400$...
    >: hi guys
    >: i m trying to fetch current week's (i.e. today + 6 days
    >: more ) records using sql
    >:
    >: sql3="Select * From tblEndMinTerm WHERE DateEnd >=

    GetDate
    >: () AND DateEnd <= DATEADD(day,6,GetDate()) Order By
    >: DateEnd Desc"
    >:
    >: but it doesnt display todays record however it display
    >: data from tomorro's date...
    >:
    >: i think its problem with time field ..i need to specify
    >: from 00:00:00 to 23:59:59 with start date and end

    date..
    >:
    >: can any one help me..
    >: thanx..
    >: dave
    >
    >
    >.
    >
    dave, May 3, 2004
    #3
  4. dave

    Ken Schaefer Guest

    What do you mean, "not working"?

    I made a mistake in the syntax (swap VarChar and GetDate() around), but you
    would have found that out if you'd check in SQL Server Books Online.

    Cheers
    Ken


    "dave" <> wrote in message
    news:76d301c430ce$8d560e80$...
    : no not working...
    : can u suggest me how to put 00:00:00 and 23:59:59 between
    : start and end date???
    :
    : thanx
    : dave
    : >-----Original Message-----
    : >WHERE DateEnd >= CAST(CONVERT(GetDate(), varChar, 112)
    : AS SmallDateTime)
    : >
    : >etc
    : >
    : >Converting to varchar in the specified format, drops the
    : hh:mm:ss, and then
    : >you convert it back to smallDateTime
    : >
    : >Cheers
    : >Ken
    : >
    : >
    : >"dave" <> wrote in
    : message
    : >news:746301c430c6$49ae6400$...
    : >: hi guys
    : >: i m trying to fetch current week's (i.e. today + 6 days
    : >: more ) records using sql
    : >:
    : >: sql3="Select * From tblEndMinTerm WHERE DateEnd >=
    : GetDate
    : >: () AND DateEnd <= DATEADD(day,6,GetDate()) Order By
    : >: DateEnd Desc"
    : >:
    : >: but it doesnt display todays record however it display
    : >: data from tomorro's date...
    : >:
    : >: i think its problem with time field ..i need to specify
    : >: from 00:00:00 to 23:59:59 with start date and end
    : date..
    : >:
    : >: can any one help me..
    : >: thanx..
    : >: dave
    : >
    : >
    : >.
    : >
    Ken Schaefer, May 3, 2004
    #4
  5. dave

    roger Guest

    "dave" <> wrote in message
    news:76d301c430ce$8d560e80$...

    > no not working...


    ?


    Does this work?

    WHERE DateEnd >=

    cast(cast(GetDate() as int) as datetime)
    AND DateEnd <
    cast((cast(GetDate() as int) + 7) as datetime)

    Note:
    use '<' not '<='
    and
    add 7 days, not 6

    If it doesn't work, please say...

    what happened when you tried it
    database (I'm guessing SQLServer)
    database version
    data type of "DateEnd"

    --
    roger
    roger, May 3, 2004
    #5
  6. Aaron Bertrand - MVP, May 3, 2004
    #6
    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. Chris Berg
    Replies:
    0
    Views:
    785
    Chris Berg
    Oct 27, 2003
  2. Peter Grison

    Date, date date date....

    Peter Grison, May 28, 2004, in forum: Java
    Replies:
    10
    Views:
    3,244
    Michael Borgwardt
    May 30, 2004
  3. Keith Cochrane
    Replies:
    2
    Views:
    609
    Keith Cochrane
    Aug 6, 2006
  4. flamesrock
    Replies:
    8
    Views:
    449
    Hendrik van Rooyen
    Nov 24, 2006
  5. Summu82
    Replies:
    5
    Views:
    861
    Richard Heathfield
    Jun 7, 2006
Loading...

Share This Page