How to convert excel imported decimal date value to real date in SQL Server

Suppose we have one excel sheet that we have imported into a table i.e. “Emp” after records are imported from excel to the table through import wizard date changes to some decimal value now condition is to convert that decimal value to the real date.

Query: select Id, CreatedDate from Emp

O/P:

Id	CreatedDate
101	42741.630556

Here we can see my CreatedDate column changed to some decimal value now we need to convert it into a real date i.e provided in an excel file.

Query: select Id, CreatedDate,

              CAST(CAST(CreatedDate AS FLOAT) -2 AS datetime) as RealDate

      from Emp

O/P: 

Id	CreatedDate	RealDate
101	42741.630556	2017-01-06 15:08:00.040

What other ideas can you add to this post that I may have not mentioned?

In SQL Server DB how to add a comma between Address Details

Suppose we have a condition to add comma between Address1, Address2, City, State, and Zip But add comma only when value is available on that particular column.

 E.g. we have one table i.e. AddressDetails as shown below

Id	Address1	Address2	City	State	Zip
101	5 Wesley	Hermosa	Johnson	Tennessee	123
102	2301 MCLain		New Madison		456

 

Query:

select  Id,

ISNULL(NULLIF(Address1, '') + ',', '') +

ISNULL(NULLIF(Address2, '') + ',', '') +

ISNULL(NULLIF(City, '') + ',', '') +

ISNULL(NULLIF(State, '') + ',', '') +

ISNULL(NULLIF(Zip, '') + '', '')-----Remove last column comma

As Address

from AddressDetails

O/P:-

Id	Address
101	5 Wesley, Hermosa, Johnson,  Tennessee, 123
102	2301 MCLain, New Madison, 456

Here for Id=102 Address2 and State column is empty but still no extra column was added into the result set.

 

What other ideas can you add to this post that I may have not mentioned?