Convert from Excel "date code"

Asked

Viewed 231 times

3

good afternoon!

I need to import an excel spreadsheet to SQL and in this spreadsheet I have many date fields.

In excel I visualize the date this way "30/01/2020", but when I impose this information in the database, comes this code "43860".

Does anyone know the logic of this code, so I can do a reverse logistics? (transform SQL code 43860 30/01/2020).

Thank you!

Solved

Thank you all!

inserir a descrição da imagem aqui

Rectifying: For some reason the account must be DAY -2

Following are examples:

inserir a descrição da imagem aqui

It seems that over that time some date does not appear on the calendar (sometimes it may be due to leap year).

In the above example, the date hit only when I typed (data_x - 2, '1900/01/01') But if I make that same logic with the date itself '1900/01/01' the result would be '1899-12-31 00:00:00.000'

sql database excel date

  • Just change the format of the general cell to Data Hora and choose the display format of your choice. Excel stores dates in the June day format, i.e., the number of days elapsed from a specific date.

  • This number is the number of days counted from 01/01/1900: https://www.myonlinetraininghub.com/excel-date-and-time#_Toc495931389 - if I’m not mistaken, just format the column, changing the type to "Date", which it shows correctly

  • Yes, I can correct that at hand, but it will be a recurring import and I would like to automate without having to put my hand in, you know? So I would like to know how to fix directly in SQL But thanks for the personal help :) hkotsubo, thanks! I’ll validate to see if it works out and come back here to talk.

  • Also because I have many date fields, are more than 10 columns in the middle of 52 with other information. I want to reduce manual interventions as much as possible

  • Just select all columns containing dates (with CTRL) and format them at once.

  • Guys, I got it with the dateadd function

  • Thank you to everyone who helped :)

  • Microsoft was warned many years ago of this issue but they did not correct justifying that it was the way the Lotus-123 did and they wanted to maintain compatibility even if wrong.

  • @Bruna Favor [Dit] the question and withdraw the answer. E answer the question itself

  • Done. Thank you! :)

Show 5 more comments

1 answer

0

SOLVED 2

I found a BUG in excel regarding the date

Excel counts the day 02/29/1900 as date, but this date does not exist according to the official calendar.

SQL does not count as a date, so there is this difference in numbers (from -1 to -2)

I mean, it’s validated.

The formula we can use in this case is:

declare @data_excel int set @data_excel = 43860

SELECT DATEADD(day, @data_excel-2, '1900/01/01') AS Dateadd;

Browser other questions tagged sql database excel date

You are not signed in. Login or sign up in order to post.