If there is the serial number, such as 39224, how do I convert it to a format of day-month (ex 5-May) I understand the aesthetics of the serial number, being the number of days since Jan 1 1900. But I can only figure out how to do either the month, day, OR year. I need to do Day AND month. Convert serial number to date with formula. You can also convert serial number to date with formula in Excel. Please do as follows. Select a blank cell (says cell B2) adjacent to the serial number cell you need to convert to date, then enter formula =TEXT(A2,'m/d/yyyy').

1. Convert Number To Date Time In Excel
2. Convert Serial Date To Text Excel
3. Excel Convert Date To Serial
4. Convert Date To Serial Number
5. How To Convert Serial Number To Date In Excel Form

I got a column called DateOfBirth in my csv file with Excel Date Serial Number Date

Example:

When i formatted cells in excel these are converted as

I need to do this transformation in SSIS or in SQL. How can this be achieved?

12 Answers

In SQL:

In SSIS, see here

The marked answer is not working fine, please change the date to '1899-12-30' instead of '1899-12-31'.

You can cast it to a SQL smalldatetime:

SQL Server counts its dates from 01/01/1900 and Excel from 12/30/1899 = 2 days less.

this actually worked for me

(minus 1 more day in the date)

referring to the negative commented post

Found this topic helpful so much so created a quick SQL UDF for it.

SSIS Solution

'The DT_DATE data type is implemented using an 8-byte floating-point number. Days are represented by whole number increments, starting with 30 December 1899, and midnight as time zero. Hour values are expressed as the absolute value of the fractional part of the number. However, a floating point value cannot represent all real values; therefore, there are limits on the range of dates that can be presented in DT_DATE.' Read more

From the description above you can see that you can convert these values implicitly when mapping them to a DT_DATE Column after converting it to a 8-byte floating-point number DT_R8.

Use a derived column transformation to convert this column to 8-byte floating-point number:

Then map it to a DT_DATE column

Or cast it twice:

You can check my full answer here:

I had to take this to the next level because my Excel dates also had times, so I had values like this:

(also, to complicate it a little more, my numeric value with decimal was saved as an NVARCHAR)

The SQL I used to make this conversion is:

In addition of @Nick.McDermaid answer I would like to post this solution, which convert not only the day but also the hours, minutes and seconds:

For example

• 42948.123 to 2017-08-01 02:57:07.000
• 42818.7166666667 to 2017-03-24 17:12:00.000

You can do this if you just need to display the date in a view:

CAST will be faster than CONVERT if you have a large amount of data, also remember to subtract (2) from the excel date:

If you need to update the column to show a date you can either update through a join (self join if necessary) or simply try the following:

You may not need to cast the excel date as INT but since the table I was working with was a varchar I had to do that manipulation first. I also did not want the 'time' element so I needed to remove that element with the final cast as 'date.'

If you are unsure of what you would like to do with this test and re-test! Make a copy of your table if you need. You can always create a view!

Convert Number To Date Time In Excel

Try the approach discussed in the following link, which involves creating a function that performs the converstion and applying the function in your SQL.

This worked for me because sometimes the field was a numeric to get the time portion.

Command:

Not the answer you're looking for? Browse other questions tagged sqlsql-servertsqlssisetl or ask your own question.

I can't seem to find an answer to this anywhere --- I want to convert a datetime in SQL to the excel serial number.

I'm essentially looking for the DATEVALUE function from excel but for use in SQL

Convert Serial Date To Text Excel

Any ideas on how to do this? thanks

3 Answers

Assuming the desired date is 2016-05-25

Returns

If you want the time portion as well

Returns

You just need to convert your datetime value to int and add 1:

With collation: SQL_Latin1_General_CP1_CI_AS

you should use

Excel Convert Date To Serial

Returns 42515

You could replace '20160525' for getdate() or you date field

Convert Date To Serial Number

How To Convert Serial Number To Date In Excel Form

Not the answer you're looking for? Browse other questions tagged sqlsql-servertsqlsql-server-2012date-manipulation or ask your own question.