Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2006
    Posts
    87

    Unanswered: problem with getting results with a given date

    Hi everybody have this query to get results of a list of persons arriving 5 days from today...

    DECLARE @TODAY DATETIME
    DECLARE @TOMMOROW DATETIME

    SET @TODAY=GETDATE()
    SET @TOMMOROW=DateAdd("d",5,@Today)

    SELECT * FROM TBLVIAGGI
    WHERE DTVIAGEDATE=@TOMMOROW

    this query doesn't give me any result inspite of having persons which will arrive on the 14th of april dtviagedate is a datetime field and the format is 2009-04-14 00:00:00.000 (yyyy-mm-dd) is there something wrong with my query?

    thanks

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Yep - you've forgotten that GETDATE() includes the date AND time.

    Isn't "tomorrow" a really bad name for a variable that is set 5 days into the future?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Aug 2006
    Posts
    87
    thanks for that even the tommorow variable will change it since it is confusing...

    if getdate() includes time is there a way I could use it to return a result or is there another function that i could use?

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    You can strip time portion from GETDATE:
    Code:
    SELECT    DATEADD(d, DATEDIFF(d, '19701212', GETDATE()), '19701212')
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Aug 2006
    Posts
    87
    Quote Originally Posted by pootle flump
    You can strip time portion from GETDATE:
    Code:
    SELECT    DATEADD(d, DATEDIFF(d, '19701212', GETDATE()), '19701212')


    THANKS GOT IT NOW....

  6. #6
    Join Date
    Nov 2004
    Location
    Canada
    Posts
    58
    DECLARE @TODAY DATETIME
    DECLARE @TOMMOROW DATETIME

    SET @TODAY=GETDATE()
    SET @TOMMOROW=DateAdd("d",5,@Today)

    SELECT * FROM TBLVIAGGI
    WHERE convert(int,convert(char(8),DTVIAGEDATE,112))= convert(int,convert(char(8),@TOMMOROW,112))
    Converts the dates to an integer (8 character format which will remove the time factor)
    ie. 20090509

    Select convert(int,convert(char(8), getdate(),112))
    --run this in query analyzer

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by burkular
    DECLARE @TODAY DATETIME
    DECLARE @TOMMOROW DATETIME

    SET @TODAY=GETDATE()
    SET @TOMMOROW=DateAdd("d",5,@Today)

    SELECT * FROM TBLVIAGGI
    WHERE convert(int,convert(char(8),DTVIAGEDATE,112))= convert(int,convert(char(8),@TOMMOROW,112))
    Converts the dates to an integer (8 character format which will remove the time factor)
    ie. 20090509

    Select convert(int,convert(char(8), getdate(),112))
    --run this in query analyzer
    But the stored date doesn't contain time portions, so this just removes the use of any index if it exists.
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •