none
Convert Data with case when statement

    Question

  • Dear All,

    May i know how to convert data with case when statement ?

    Example as below 

    SELECT CASE WHEN CONVERT(varchar(8),getdate(),112) IS TRUE THEN convert(varchar(8),EMPLEAVE.START_DAY,112) BETWEEN CONVERT (varchar(8),EMPLEAVE.END_DAY,112) ELSE FALSE END AS LEAVE

    FROM TABLE


    Wednesday, September 25, 2013 9:36 AM

Answers

  • If START_DAY and END_DAY are date columns, and you only want to allow rows where today is in that date range, you can simply use this:

    WHERE CAST(CURRENT_TIMESTAMP AS date) BETWEEN START_DAY AND END_DAY

    If you are on SQL Server 2005 (or earlier) or for any other reason have declared START_DAY and END_DAY as datetime, but they actually contain dates (without time portions), then you can use this

    WHERE CURRENT_TIMESTAMP >= START_DAY
    AND   CURRENT_TIMESTAMP <  DATEADD(day, 1, END_DAY)

    If - for every row - you need to know whether or not today is in the date range, you can simply wrap this code in a CASE expression. For example:

    CASE WHEN CAST(CURRENT_TIMESTAMP AS date)
              BETWEEN START_DAY
              AND     END_DAY
         THEN 'Y'
         ELSE 'N'
    END--CASE


    Gert-Jan

    Wednesday, September 25, 2013 10:59 AM
  • Try

    select case when getdate() between EMPLEAVE.START_DAY and EMPLEAVE.END_DAY then 'true' else 'false' end as Leave

    from table1,...



    Many Thanks & Best Regards, Hua Min



    Wednesday, September 25, 2013 9:40 AM

All replies

  • Try

    select case when getdate() between EMPLEAVE.START_DAY and EMPLEAVE.END_DAY then 'true' else 'false' end as Leave

    from table1,...



    Many Thanks & Best Regards, Hua Min



    Wednesday, September 25, 2013 9:40 AM
  • May be you want some thing like this ,

    SELECT CASE WHEN CONVERT(varchar(8),getdate(),112)  between convert(varchar(8),EMPLEAVE.START_DAY,112) and CONVERT (varchar(8),EMPLEAVE.END_DAY,112)
            then 'TRUE' ELSE 'FALSE' END  AS LEAVE
    FROM [TABLE]

    But the BETWEEN with varchar may not not give desired result , why don't you try DATE type?

    SELECT CASE WHEN cast(getdate() AS Date)  between cast( EMPLEAVE.START_DAY AS Date) and cast(EMPLEAVE.END_DAY AS Date)
            then 'TRUE' ELSE 'FALSE' END  AS LEAVE
    FROM [TABLE]


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Wednesday, September 25, 2013 9:41 AM
  • The query you posted is confusing. Could you explain what you are trying to do? This is a typical case statement

     SELECT CASE WHEN EMPLEAVE.START_DAY is NULL THEN 'NO DATE' ELSE 
            CONVERT (varchar(8),EMPLEAVE.END_DAY,112) END  AS LEAVE


    Satheesh
    My Blog



    Wednesday, September 25, 2013 9:41 AM
  • If START_DAY and END_DAY are date columns, and you only want to allow rows where today is in that date range, you can simply use this:

    WHERE CAST(CURRENT_TIMESTAMP AS date) BETWEEN START_DAY AND END_DAY

    If you are on SQL Server 2005 (or earlier) or for any other reason have declared START_DAY and END_DAY as datetime, but they actually contain dates (without time portions), then you can use this

    WHERE CURRENT_TIMESTAMP >= START_DAY
    AND   CURRENT_TIMESTAMP <  DATEADD(day, 1, END_DAY)

    If - for every row - you need to know whether or not today is in the date range, you can simply wrap this code in a CASE expression. For example:

    CASE WHEN CAST(CURRENT_TIMESTAMP AS date)
              BETWEEN START_DAY
              AND     END_DAY
         THEN 'Y'
         ELSE 'N'
    END--CASE


    Gert-Jan

    Wednesday, September 25, 2013 10:59 AM