Results 1 to 10 of 10
  1. #1
    Join Date
    Jan 2004
    Location
    Montreal, Canada
    Posts
    151

    Arrow Unanswered: Date an Time conversion problem

    Well here is the problem iam trying to evaluate a expresion and return a string but when i run my code it always return where my expresion is false where it should return true. here is my code.

    BEGIN
    DECLARE @datefin_flag char(13), @strip datetime
    select @strip = getdate()
    --select convert(char(10),@strip,120)
    select @datefin_flag = dateend FROM mattstest WHERE convert(char(10),datebegin,120) <= convert(char(10),@strip,120) and convert(char(10),dateend,120) >= convert(char(10),@strip,120)
    --select @datefin_flag
    --UPDATE dateflagevent SET flagevent = getdate() FROM dateflagevent
    IF (@datefin_flag = @strip)
    BEGIN
    print 'Run'
    END
    ELSE
    print 'You cant run this'
    END


    Now here is the my table data:

    datebegin datefin
    ------------------------ ------------------------
    2004-12-25 00:00:00.000 2005-01-25 00:00:00.000
    2004-11-25 00:00:00.000 2004-12-24 00:00:00.000
    2005-02-25 00:00:00.000 2005-03-25 00:00:00.000

    I think that the problem is the date and time they are the same but not in the right format its like saying 2004-01-25 is equal to janv 25 2005 how do i correct this.

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    your problem might be you are doing a >= comparison on character data due to your convert functions
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Jan 2004
    Location
    Montreal, Canada
    Posts
    151
    Can you show me in code.

  4. #4
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    I am going to be brief because I am about to go home and I am tired of goofing off at work on this forum today but I think your problem is that you want to compare two dates but your are converting your date values to character strings.

    convert(char(10),datebegin,120) <= convert(char(10),@strip,120) and convert(char(10),dateend,120) >= convert(char(10),@strip,120)

    I believe when you do this (I have not double checked so flame me all you want smart guys) you are actually comparing the unicode value of the 2 strings you just created.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Correct, except that format 120 is "yyyy-mm-dd", so that even as character data it should sort correctly. (I'll need to double-check that in the morning...)

    I think your problem is that you are assigning the value getdate() to @strip, and getdate() includes both a date AND a time value. So later when you compare it to the value selected from your table (dateend? datefin?), it is only going to match if run at precisely midnight.

    Try this assignment:
    select @strip = CONVERT(CHAR(10), getdate(), 120)
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Jan 2004
    Location
    Montreal, Canada
    Posts
    151
    I think it realy is the time associated with the date but how do i strip it out or how do i evaluate my expression ?

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    select @strip = CONVERT(CHAR(10), getdate(), 120).

    select @strip = CONVERT(CHAR(10), getdate(), 120).

    select @strip = CONVERT(CHAR(10), getdate(), 120).

    select @strip = CONVERT(CHAR(10), getdate(), 120).

    select @strip = CONVERT(CHAR(10), getdate(), 120).

    select @strip = CONVERT(CHAR(10), getdate(), 120).

    select @strip = CONVERT(CHAR(10), getdate(), 120).

    select @strip = CONVERT(CHAR(10), getdate(), 120).

    select @strip = CONVERT(CHAR(10), getdate(), 120).

    select @strip = CONVERT(CHAR(10), getdate(), 120).

    select @strip = CONVERT(CHAR(10), getdate(), 120).

    select @strip = CONVERT(CHAR(10), getdate(), 120).

    select @strip = CONVERT(CHAR(10), getdate(), 120).

    select @strip = CONVERT(CHAR(10), getdate(), 120).

    select @strip = CONVERT(CHAR(10), getdate(), 120).

    select @strip = CONVERT(CHAR(10), getdate(), 120).
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    instead of converting datebegin and dateend to strings (which will mean that any indexes on those columns will be ignored, you get a table scan), why not do the query based on datetime comparisons, based on the current date which you can obtain by stripping the time portion out of getdate() while leaving the result as a datetime value

    specifically,
    Code:
    -- strip time component from today, but leave as datetime
    select @strip = dateadd(d,datediff(d,0,getdate()),0)
    
    -- search based on today's date
    select @datefin_flag = dateend 
      from mattstest 
     where datebegin <= @strip
       and dateend >= @strip
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Jan 2004
    Location
    Montreal, Canada
    Posts
    151
    thanx dude worked perfect

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    select @strip = dateadd(d,datediff(d,0,getdate()),0).

    select @strip = dateadd(d,datediff(d,0,getdate()),0).

    select @strip = dateadd(d,datediff(d,0,getdate()),0).

    select @strip = dateadd(d,datediff(d,0,getdate()),0).

    select @strip = dateadd(d,datediff(d,0,getdate()),0).

    select @strip = dateadd(d,datediff(d,0,getdate()),0).
    .
    .
    .
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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