January 22, 2019 at 8:18 am
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
January 22, 2019 at 9:20 am
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/
January 22, 2019 at 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.
January 22, 2019 at 2:06 pm
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/
January 22, 2019 at 2:09 pm
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.
January 22, 2019 at 2:10 pm
saptek9 - Tuesday, January 22, 2019 12:39 PMNo 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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply