asked on

SQL Query - Change date format & replace data with text conditionally

SQL Studio Manager 2008...

1- I need to change the data format output for dates in a column,
current date format is... YYYY-MM-DD 00:00:00:000,
I need it to be YYYYMMDD
Current syntax is.... Select tmp.Dt as 'Date' from tmp Where ...etc...
I need the correct syntax

2- I need to replace data with text conditionally,
current data is...... 10
I need it to be , id data is 10 then replace with Diamond
Current syntax is.... Select tmp.Sc as 'Ore' from tmp Where ...etc...
I need the correct syntax

Thank You!

ASKER CERTIFIED SOLUTION

edtechdba

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

SOLUTION

edtechdba

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

PortletPaul

NO points pl.

edtechdba has provided the method to display in 'YYYYMMDD'
which uses the 'display style' number 112

the formal list of these display styles is:
http://msdn.microsoft.com/en-us/library/ms187928(v=sql.105).aspx
and there are many other useful links with examples, such as this
I have my own cheat sheet :) here

regarding the 'Diamond', I'd suggest you need an "else", perhaps just outputting a null.

case when tmp.Sc = 10 then 'Diamond' else NULL end as 'Ore'

BigBadWolf_000

ASKER

PortletPaul: Thank you for the - else NULL
and the reference links :)

BigBadWolf_000

ASKER

Thanks you both :)

SQL Query - Change date format &amp; replace data with text conditionally

SQL Query - Change date format & replace data with text conditionally