Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2011
    Posts
    82

    Unanswered: How to write a date range on a case statement

    Hello,

    I have a case statement as follows which I am using in the where clause to get todays data

    (Case when (A.ID is null or B.ID = '' or C.ID= ' ') Then
    convert(varchar,(Case when A.Time is null then B.Time else A.Time end),103)

    else convert(varchar,(Case when A.Time is null then ''" else B.Time end),103) end)

    =convert(varchar,getDATE(),103)

    However I want to know whether I can use a date range on a case statement

    I.e. I want to be able to use BETWEEN statement to get a date range

    BETWEEN convert(varchar,getDATE(),103) AND convert(varchar,getDATE()-1,103)


    How do I achieve this on a CASE WHEN statment?

    Any help would be greatly appreciated

    Thanks
    Helen

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    It should not be necessary to convert you date values to strings prior to comparing them. In fact, you should avoid it. The string format you're using won't sort properly anyway.
    If you want to compare whole date values, convert to the DATE datetype:

    CASE WHEN [YourDateValue] BETWEEN convert(date,getDATE()) AND convert(date, getDATE()-1) THEN [SomeValue] ELSE [SomeOtherValue] END
    If it's not practically useful, then it's practically useless.

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

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    blindman, your BETWEEN won't work

    put the earlier date first

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Time is relative.
    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
  •