0

I am using the code below because it does what I need, but I need the result to be formatted as yyyy-mm-dd. It is formatted as a string of course as: Feb 19 2016 12:00AM, but in that case I need: 2016-02-19 instead. I am using SQL Server and can only extract from the db, can't write :(

coalesce(cast(
  case
    when lsf2.STARTDATE > lsf.STARTDATE then nph2.STARTDATE
    else NULL
  end as varchar(20)
),'') as 'StartDate'
Pred
  • 8,789
  • 3
  • 26
  • 46
Sheri G
  • 47
  • 11
  • 1
    Check out Convert function which allows styles. I never use cast, there may be a way to do it but I dont know. – Joe C Nov 27 '17 at 16:49
  • 2
    Try to format your question next time to help us to understand it. Next thing is to tag the version of SQL Server too, there are different functions and solutions available in them. – Pred Nov 27 '17 at 16:50
  • What's the data type of lsf/lsf2/nph2.STARTDATE? If it's DATETIME, see the linked duplicate. If it isn't, your comparison is probably broken. :-) – Heinzi Nov 27 '17 at 16:51
  • 1
    Possible duplicate of [How to get a date in YYYY-MM-DD format from a TSQL datetime field?](https://stackoverflow.com/questions/889629/how-to-get-a-date-in-yyyy-mm-dd-format-from-a-tsql-datetime-field) – Heinzi Nov 27 '17 at 16:52
  • 1
    Check out the help doc on `Convert()` function for tsql [here](https://learn.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql). Something like `Convert(varchar, case when lsf2.STARTDATE > lsf.STARTDATE then nph2.STARTDATE else NULL end, 120)` will probably do the trick – JNevill Nov 27 '17 at 16:54
  • Thank you. It is a date-time field that I am having to bring back as blank if there isn't a date that fits the criteria and all I was getting is the '1900-01-01' or a NULL and that is not acceptable to my end users. I tried 'convert' until I was blue in the face and couldn't get the right data out of it. The code you gave me returns "NULL" when there isn't a row with a date that meets my criteria. I can't have a NULL in the field, I need a blank. I got this new code from someone here the other day. I don't know my SQL Server version unfortunately. (unless I can find that in Studio?) – Sheri G Nov 27 '17 at 17:02
  • just found out SQL Version is 2012 – Sheri G Nov 27 '17 at 17:06
  • 1
    I think from jnevill's example if you want to have a blank instead of NULL then just replace `else NULL` with `else ''`. You have total control over what it outputs. – ADyson Nov 27 '17 at 17:10
  • Thankyou. Unfortunately, I get dates that are not accurate for my criteria if I don't use the cast(case statement in rows where I should be getting NULLs, (desired blanks) – Sheri G Nov 27 '17 at 17:30
  • 1
    What are the datatypes of lsf2.STARTDATE, lsf.STARTDATE, nph2.STARTDATE? It says date but you say something about string format. Are these actually strings labeled as dates? – Zorkolot Nov 27 '17 at 21:18
  • " I get dates that are not accurate". It would help if you gave an example of the input data, and the output you got, and the output you expected. This description is a little bit vague, to say the least. Also it's hard to see how you'd be having this problem unless, as Zorkolot mentioned, you're actually storing your dates in varchar fields? Which, by the way, is a terrible idea because you start to run into issues like this. Date strings are just that - human-readable text representations of the actual unique date stamp. They are not, by and large, easily understood by machines. – ADyson Nov 27 '17 at 22:17
  • lsf2.STARTDATE, lsf.STARTDATE, nph2.STARTDATE are date-time in the db (which I have no write access to) but I have converted them to string to get rid of the '1900-01-01...' that comes back when the field doesn't exist for my row because my > criteria wasn't met. The only way around that that I've found (please give me another if you have it) is to convert it to a string. – Sheri G Nov 29 '17 at 15:18

0 Answers0