I've a varchar column with a value of 42978.6736458333 that I want to convert back to a proper datetime. I've searched quite a bit and tried many of the suggestions but I cannot seem to find one that works with the data I have.
I got this value from excel when I did a CONCATENATE of all the fields in the sheet to do an insert to my DB; the output of the datetime column looks like "42978.6736458333" (the cell originally contained "2017-08-31 16:10:03"). I tried formatting and various things in excel to no avail.
Here are a few examples of what I've tried:
Select
convert(varchar(23), date, 112) DATE1,
convert(datetime, '20160805') DATE2,
convert(datetime, '2011-09-28 18:01:00', 120) DATE3,
dateadd(second, 42978.6736458333 * 24*24*60, '1899-12-31') DATE4
From
[dbo].[trainingLog]
Results:
For every result. DATE 2/3/4 don't count up even though the original datetime varchar increments.
For example, here are more varchar values:
42981.5092361111
42982.7187615741
42983.8171527778
The above attempts return a value/date, but it's the same date even though my varchar value increments.
I expect any datetime format. I really only want the month/day/year in any format.
Try
SELECT CAST(42978.6736458333 AS DATETIME)
returns
2017-09-02 16:10:03.000
However SQL server uses 01/01/1900 as the epoch whereas your excel uses 30/12/1899 or 31/12/1899 as the epoch so it looks like you need to subtract 2 days off after you cast.
e.g.
SELECT dateadd(d, -2, CAST(42978.6736458333 AS DATETIME) )
returns
2017-08-31 16:10:03.000
From you comment I am not sure how you get your value as running
SELECT CAST(42981.5092361111 AS DATETIME)
returns for me
2017-09-05 12:13:18.000