Date Format

• sharonsql2013

  SSCertifiable

  Points: 5908

  More actions

  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

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  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

• sharonsql2013

  SSCertifiable

  Points: 5908

  More actions

  February 23, 2016 at 1:59 pm

  #1860567

  Glad you pointed that first format did make a difference.

  Thank you!