conversion from Excel format date to sql date functon

• saptek9

  Ten Centuries

  Points: 1155

  More actions

  January 22, 2019 at 8:18 am

  #376865

  I have create_date stored in sql server table in excel format with function =DATE(1980,1,1+A1-722815). I need to convert this into normal date format using sql function. Can anyone suggest?

  Example: date value 737060 to 1/1/2019

• Mike01

  SSChampion

  Points: 11413

  More actions

  January 22, 2019 at 9:20 am

  #2019661

  how does date value 737060 to 1/1/2019 ?   Are you storing in Excel or SQL Server?  What is the datatype in SQL Server? Why are you doing this?

  For better, quicker answers, click on the following...
  http://www.sqlservercentral.com/articles/Best+Practices/61537/

  For better answers on performance questions, click on the following...
  http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

• saptek9

  Ten Centuries

  Points: 1155

  More actions

  January 22, 2019 at 12:39 pm

  #2019715

  No excel here. sqlserver integer value to normal date and normal date to integer conversion. that integer value is stored in sql server table. I can able to convert using following function:
  select dateadd(d,[date]-722815,'1980-1-1')

  but, now i need to write a function to convert back to integer to insert the int value for new dates.

• Mike01

  SSChampion

  Points: 11413

  More actions

  January 22, 2019 at 2:06 pm

  #2019736

  I don't know how or what [date] is in your query, but these 2 fail. Can you provide sample date that works

  select dateadd(d,getdate()-722815,'1980-1-1')
select dateadd(d,cast(getdate() as date) -722815,'1980-1-1')

  For better, quicker answers, click on the following...
  http://www.sqlservercentral.com/articles/Best+Practices/61537/

  For better answers on performance questions, click on the following...
  http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

• saptek9

  Ten Centuries

  Points: 1155

  More actions

  January 22, 2019 at 2:09 pm

  #2019738

  i got the answer both convert back and forth. Here is the example: 
  select dateadd(d,737060 -722815,'1980-1-1')
  select datediff(d, 0, '2019-01-01') + 693596
  Thanks for trying to answer.

• Jeffrey Williams

  SSC Guru

  Points: 90348

  More actions

  January 22, 2019 at 2:10 pm

  #2019739

  saptek9 - Tuesday, January 22, 2019 12:39 PM

  No excel here. sqlserver integer value to normal date and normal date to integer conversion. that integer value is stored in sql server table. I can able to convert using following function:
  select dateadd(d,[date]-722815,'1980-1-1')

  but, now i need to write a function to convert back to integer to insert the int value for new dates.

  Is this what you are looking for?

  Declare @excelSeed date = '1980-01-01'
      , @excelDateOffset int = 722815;

  Select dateadd(day, 737060 - @excelDateOffset, @excelSeed)
      , datediff(day, @excelSeed, '2019-01-01') + @excelDateOffset;

  You don't really need a function - you just need the calculation.  You could create a computed column so you always have that value available...

       If object_id('tempdb..#myTable', 'U') Is Not Null
        Drop Table #myTable;
     Go

  Create Table #myTable (
        create_date date
      , excel_int_date As datediff(day, '1980-01-01', create_date) + 722815
        );
     Go

  Insert Into #myTable
Values ('1990-01-01')
      , ('2019-01-01');

  Select *
   From #myTable mt;

  Jeffrey Williams
  “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

  ― Charles R. Swindoll

  How to post questions to get better answers faster
  Managing Transaction Logs