Format the output as Date

  • Hello,

    I have the following code and the result set is coming out as nvarchar. So, when I create a report in SSRS, it is not formatting as a date. How should I approach this? I have bing'd and google'd but no help. Thank you!

    , CASE

    WHEN isnull(cv2.Accepted,0)='True' AND cv2.Visit ='V2' AND cv2.StepNo='3' THEN 'Y'

    ELSE CONVERT(varchar,[dbo].[fn_Get_WorkingDays] (co.PlannedGoLiveDt, -10))

    END AS 'System Verified'

    And, I get the following result.

    Feb 18 2014 12:00AM

    Sincerely,

    SJ

  • Sanjeev Jha-475652 (2/28/2014)


    Hello,

    I have the following code and the result set is coming out as nvarchar. So, when I create a report in SSRS, it is not formatting as a date. How should I approach this? I have bing'd and google'd but no help. Thank you!

    , CASE

    WHEN isnull(cv2.Accepted,0)='True' AND cv2.Visit ='V2' AND cv2.StepNo='3' THEN 'Y'

    ELSE CONVERT(varchar,[dbo].[fn_Get_WorkingDays] (co.PlannedGoLiveDt, -10))

    END AS 'System Verified'

    And, I get the following result.

    Feb 18 2014 12:00AM

    Sincerely,

    SJ

    You have two options:

    1) Change the case statement to evaluate to a date

    To do this, all outcomes of the case (every THEN and ELSE) need to evaluate to the same type.

    So where you have the THEN 'Y' clause, you'd need to replace that with a datetime or null value, then you could take the CONVERT out of the ELSE.

    2) Set the format of the date when you convert it to a string.

    You'll have to do this if the 'Y' is a valid entry.

    Try changing your convert statement to:

    CONVERT(varchar,[dbo].[fn_Get_WorkingDays] (co.PlannedGoLiveDt, -10),101)

    for an idea of what can be done. The 101 specifies the format to show the date in. You can find a list of formats you could specify here

  • Hi Nevyn,

    I appreciate both of your suggestions. For this specific task, option #2 worked great for me. Although, I can use Step #1 for some other opportunities. I thank you not only for helping me solve my problem but also for teaching me learn some of the ways to solve tsql problems. Have a wonderful weekend!!

    Sincerely,

    SJ

  • Hi,

    I posted a separate post in BI Group but have not received any updates so I am trying my luck here in the TSQL forum. I apologize for breaking the forum rules and guidelines.

    Link to the orig post is here:

    http://www.sqlservercentral.com/Forums/Topic1546543-147-1.aspx?Update=1

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply