Post reply

Date Format

  • February 23, 2016 at 1:46 pm

    #305020

    I have this Query in the data set . The issue is even though the format is Date when its executed in the data set it shows up as Datetime

    So instead of showing as 2016-02-01 it shows 2016-02-01 12:00:00:00 AM.

    Since its a parameter and not a text I don't know where can I change the format again.

    Please advice

    Query :

    With CTE1 AS

    (SELECT CAST(DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)AS DATE) AS StartDate

    UNION

    SELECT FORMAT(DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())-1, 0),'yyyy-MM-dd') AS StartDate

    UNION

    SELECT FORMAT(DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())-2, 0),'yyyy-MM-dd') AS StartDate

    UNION

    SELECT FORMAT(DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())-3, 0),'yyyy-MM-dd') AS StartDate)

    SELECT StartDate FROM CTE1

    ORDER BY StartDate DESC

  • February 23, 2016 at 1:53 pm

    #1860562

    You didn't format the first row. Format will convert the values into strings.

    I don't remember if it's possible to assign a format to the parameter available values, to use a single date column. If it's not possible, I know that there's a Value and a Display property for the parameter. The display should have the formatted string, while the value should have the date column.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • February 23, 2016 at 1:59 pm

    #1860567

    Glad you pointed that first format did make a difference.

    Thank you!

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

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