0

I have this in my Excel file that I receive : 1/1/1980 2:34:52 PM and after importing it's now stored as 29221.6075462963.

The database field is a nvarchar (200). I need a query that just shows exactly like excel : 1/1/1980 2:34:52 PM.

I am using SQL server 17.

Alexandre Elshobokshy
  • 10,720
  • 6
  • 27
  • 57
SQL Rookie
  • 11
  • 4
  • 1
    You should sort your import out so that it imports into a column of appropriate dataype but this should give you a clue how to fix the data that is already imported `SELECT CAST(CAST(N'29221.6075462963' AS FLOAT) -2 AS datetime)` – Martin Smith Feb 28 '20 at 13:43
  • The Excel file already contains dates, not decimals. What you assumed was a decimal is the OADate format used by VB6, COM and Excel. – Panagiotis Kanavos Feb 28 '20 at 13:45
  • Does this answer your question? [convert Excel Date Serial Number to Regular Date](https://stackoverflow.com/questions/13850605/convert-excel-date-serial-number-to-regular-date) – Jeroen Mostert Feb 28 '20 at 13:45
  • `shows exactly like excel : 1/1/1980` that's not what Excel does. Excel is displaying the date value using the cell's style. The date value remains the same even if you change the style. How did you import the Excel file? That's where the bug is. Whatever technique you used, it should be modified to load that column as a date, and store it in a `datetime` or `datetime2` field – Panagiotis Kanavos Feb 28 '20 at 13:48
  • If you use SSIS or SSMS's `Import Data` wizard, the column type should be detected as a `datetime` automatically. If it's not, you have to modify the detected file type. – Panagiotis Kanavos Feb 28 '20 at 13:49
  • @JeroenMostert that's not a good duplicate. The question itself confuses OADate with serial date numbers (which had different meanings in different Excel versions) and all of the answers except a couple about about SSIS and the actual format, use the wrong explanation or forumal this is an OADate, not a serial number. – Panagiotis Kanavos Feb 28 '20 at 14:02
  • 1
    @sainathreddy but *why do that at all*? You can load the data correctly and the problem will go away – Panagiotis Kanavos Feb 28 '20 at 14:03

1 Answers1

1

In general, you can use something like this:

select dateadd(second,
               floor((v.edt - floor(v.edt)) * 60*60*24),
               dateadd(day, floor(v.edt), '1899-12-30')
              )
from (values (29221.6075462963)) v(edt);
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • 1
    Why so complex? A straight `select cast(29221.6075462963 -2 as datetime)` would work. `datetime` behaves almost like an OADate. That's why `datetime` arithmetic works when `datetime2` arithmetic doesn't. It would be better to fix the real problem though, the import step – Panagiotis Kanavos Feb 28 '20 at 13:58
  • @PanagiotisKanavos . . . I tend to avoid dependence on internal date formats. – Gordon Linoff Feb 28 '20 at 14:18