Menu
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
Any ideas on how to do this? thanks
553
Also, you can apply the DATEVALUE function to convert date cells to serial numbers. Select a blank cell which will place the serial number, type this formula =DATEVALUE("8/8/2017"), press Enter key. Now the date in formula will be displayed as serial number.
From the Formulas tab, select Date & Time > DATEVALUE. The Function Arguments screen appears. You do not have to re-enter the date in the Date_text field box. Just click the text-date in cell A3, and the Excel serial number appears in cell B5.
You can specify the format of the dates in your statements using CONVERT and FORMAT. For example: select convert(varchar(max), DateColumn, 13), format(DateColumn, 'dd-MMM-yyyy')
Assuming the desired date is 2016-05-25
Select DateDiff(DD,'1899-12-30','2016-05-25')
Returns
42515
If you want the time portion as well
Declare @Date datetime = '2016-05-25 20:00'
Select DateDiff(DD,'1899-12-30',@Date)+(DateDiff(SS,cast(@Date as Date),@Date)/86400.0)
Returns
42515.8333333
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
