Convert Data Type within a Select Query and while using a Case When Query

Transact-SQL
1

Hey all,

I am trying to do something that I feel is pretty simple but can't find the answer anywhere.

I'm bringing in columns from an existing table, I'm also adding a case when condition to a few of these columns and I also need to convert the data types of those fields.

A section of what I have right now before converting the data type is this:

Select
Close_Type,
(Case When [Group]=null then 'Unknown' else [GROUP] end) as
[Group],
Group_Flag

From blah
Where blah
Order by blah;

How do I bring the Group column in as a date while also using the case where/then/else?

I tried to put this in front as this is how I've converted the columns that don't require conditions (I need UK date format)...

convert(varchar, StatusChangeDate, 103)

But it brings up Incorrect syntax near the keyword 'Case'.

Help plzzz! :slight_smile:

Thanks,
Louise

2

can you try this

Select
Close_Type,
COALESCE(CONVERT(VARCHAR(20),[Group],103),'Unknown')
[Group],
Group_Flag
From blah
Where blah
Order by blah;

3

@ahmeds08 Okay... I am a total idiot - I didn't even need to change that column, that isn't one of my date ones! - Could you give me the workaround for this example:

(Case When Valid__Start_Date='19000101' then null else Valid__Start_Date end) as Valid__Start_Date,

Thank you so much!

I have another query too... I want to re write this piece of code from SAS to SQL server....

case when Last_opp_date < today() then 'Breached'
when Last_opp_date > today()+13 then '>14 Days'
else put(( Last_opp_date ),date9.) end
as Last_opp_Display,

Fairly simple in SAS but so new to SQL - need to figure out how to say "today - something" and also how to wrap that in a datatype change too! I'm using server 2008 also.

Thank you!

4

There are few other functions you should consider:

NULLIF(Valid__Start_Date, '19000101')  --sets to NULL IF matches 19000101
COALESCE(Valid__Start_Date, '19000101')  -- sets to 19000101 if column is NULL

To get today:

GETDATE()  -- returns current date and time as a datetime data type
SYSDATETIME() --returns current date and time as a datetime2 data type
CAST(getdate() AS date)  -- returns current date as date data type
DATEADD(day, DATEDIFF(day, 0, getdate()), 0)  -- returns current date at time 00:00:00.000

To add to a date:

DATEADD(day, 13, CAST(getdate() AS date))  --today + 13 days

And finally, the data type returned is based on the function and data type precedence. For NULLIF the data type returned will be the same as the column, for COALESCE the data type returned will be the highest precedence of all columns supplied (can also use ISNULL which returns the data type of the column used), for DATEADD returns a date, datetime or datetime2 depending on the data type of the expression used.

Note: when working with datetime or datetime2 data types - don't forget that there is a time portion and any conditional checks need to consider the time. Adding a day to GETDATE() will return the date with the same time - not the date at time 00:00:00.000, which can cause issues if you do not account for the time.