Converting C++ Unix time_t Julian date to SQL date

I've just been trying to do this. I looked on Google and it seems to
be a common problem with no obvious solution. I've seen various
solutions which don't seem exactly elegant, so I figured I'd post the
solution I came up with. It's effectively a single line solution,
albeit with various embedded calls. It makes an adjustment for the
local timezone too!
DECLARE @.time_t INT
SET @.time_t = 1090834321 /* 10:32am, July 26th 2004 BST */
DECLARE @.timestamp DATETIME
SET @.timestamp = dateadd (ss, datediff (ss, GetUTCDate(), GetDate()),
dateadd (ss, @.time_t, '19700101'))
PRINT @.timestamp
Hope that helps. If there's a problem with it, let me know!
andytHi Andy,
I have received a data file and it has julian dates. How Do I convert them
to normal dates? I tried this code and replaced the variable with the date I
received, and All it did was give me the same date with different times.
Any ideas? What does the hardcoded '1970-01-01' do?
Thanks
Noma-Gcina
nmtshontshi@.deloitte.com
"Andy Turner" wrote:

> I've just been trying to do this. I looked on Google and it seems to
> be a common problem with no obvious solution. I've seen various
> solutions which don't seem exactly elegant, so I figured I'd post the
> solution I came up with. It's effectively a single line solution,
> albeit with various embedded calls. It makes an adjustment for the
> local timezone too!
>
> DECLARE @.time_t INT
> SET @.time_t = 1090834321 /* 10:32am, July 26th 2004 BST */
> DECLARE @.timestamp DATETIME
> SET @.timestamp = dateadd (ss, datediff (ss, GetUTCDate(), GetDate()),
> dateadd (ss, @.time_t, '19700101'))
> PRINT @.timestamp
>
> Hope that helps. If there's a problem with it, let me know!
>
> andyt
>|||The Unix timestamp value represents the number of seconds since 1970-01-01.
You can convert it like this:
DECLARE @.ts INTEGER
SET @.ts = 1098230400
SELECT DATEADD(SECOND,@.ts,'19700101')
(This isn't a Julian Date BTW)
David Portas
SQL Server MVP
--

Converting C++ Unix time_t Julian date to SQL date

I've just been trying to do this. I looked on Google and it seems to
be a common problem with no obvious solution. I've seen various
solutions which don't seem exactly elegant, so I figured I'd post the
solution I came up with. It's effectively a single line solution,
albeit with various embedded calls. It makes an adjustment for the
local timezone too!
DECLARE @.time_t INT
SET @.time_t = 1090834321/* 10:32am, July 26th 2004 BST */
DECLARE @.timestamp DATETIME
SET @.timestamp = dateadd (ss, datediff (ss, GetUTCDate(), GetDate()),
dateadd (ss, @.time_t, '19700101'))
PRINT @.timestamp
Hope that helps. If there's a problem with it, let me know!
andyt
Hi Andy,
I have received a data file and it has julian dates. How Do I convert them
to normal dates? I tried this code and replaced the variable with the date I
received, and All it did was give me the same date with different times.
Any ideas? What does the hardcoded '1970-01-01' do?
Thanks
Noma-Gcina
nmtshontshi@.deloitte.com
"Andy Turner" wrote:

> I've just been trying to do this. I looked on Google and it seems to
> be a common problem with no obvious solution. I've seen various
> solutions which don't seem exactly elegant, so I figured I'd post the
> solution I came up with. It's effectively a single line solution,
> albeit with various embedded calls. It makes an adjustment for the
> local timezone too!
>
> DECLARE @.time_t INT
> SET @.time_t = 1090834321/* 10:32am, July 26th 2004 BST */
> DECLARE @.timestamp DATETIME
> SET @.timestamp = dateadd (ss, datediff (ss, GetUTCDate(), GetDate()),
> dateadd (ss, @.time_t, '19700101'))
> PRINT @.timestamp
>
> Hope that helps. If there's a problem with it, let me know!
>
> andyt
>
|||The Unix timestamp value represents the number of seconds since 1970-01-01.
You can convert it like this:
DECLARE @.ts INTEGER
SET @.ts = 1098230400
SELECT DATEADD(SECOND,@.ts,'19700101')
(This isn't a Julian Date BTW)
David Portas
SQL Server MVP

Convert to Julian Date

In Oracle, we can write query to convert current date to Julian date
SELECT TO_CHAR(TRUNC(SYSDATE),'J') FROM DUAL;
This returns the value 2453754
I think there is no function in SQL Server to convert to Julian date
How do I use the same logic in SQL Server?
MadhivananMadhivanan wrote:
> In Oracle, we can write query to convert current date to Julian date
> SELECT TO_CHAR(TRUNC(SYSDATE),'J') FROM DUAL;
> This returns the value 2453754
> I think there is no function in SQL Server to convert to Julian date
> How do I use the same logic in SQL Server?
> Madhivanan
Try this:
DECLARE @.dt DATETIME ;
SET @.dt = CURRENT_TIMESTAMP ;
SELECT
DATEDIFF(DAY,'17530101',@.dt) +2361330.5
+DATEDIFF(SECOND,DATEADD(DAY,
DATEDIFF(DAY,'17530101',@.dt),'17530101')
,@.dt)/86400.00
AS julian_date ;
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||CORRECTION. Use SET @.dt = GETUTCDATE() instead of CURRENT_TIMESTAMP.
Julian dates are always expressed relative to UTC and not local time.
If you supply your own date and time you'll have to convert it to UTC
yourself.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Hi,
DECLARE @.JulianDate AS INT
select @.JulianDate = 2415021 + datediff(dd, convert(datetime,
'1900-01-01',103),getdate())
PRINT @.JulianDate
This will give the julian date and to get back the current data,
select dateadd(dd, @.JulianDate-2415021, convert(datetime, '1900-01-01',103
))
Thanks
Sree
"Madhivanan" wrote:

> In Oracle, we can write query to convert current date to Julian date
> SELECT TO_CHAR(TRUNC(SYSDATE),'J') FROM DUAL;
> This returns the value 2453754
> I think there is no function in SQL Server to convert to Julian date
> How do I use the same logic in SQL Server?
> Madhivanan
>|||Thans David,
You used the value 17530101. Is this the number of days between January
1, 4713 BC and January 1, 1900?
Sree,
What does the value 2415021 refer to?
Madhivanan|||In SQL Server the datatype DateTime can store dates in the range - January
1, 1753 through December 31, 9999
HTH. Ryan
"Madhivanan" <madhivanan2001@.gmail.com> wrote in message
news:1137583289.974927.92200@.g44g2000cwa.googlegroups.com...
> Thans David,
> You used the value 17530101. Is this the number of days between January
> 1, 4713 BC and January 1, 1900?
> Sree,
> What does the value 2415021 refer to?
> Madhivanan
>|||Madhivanan wrote:
> Thans David,
> You used the value 17530101. Is this the number of days between January
> 1, 4713 BC and January 1, 1900?
> Sree,
> What does the value 2415021 refer to?
> Madhivanan
'17530101' just represents January 1, 1753, which is just the earliest
date supported by SQL Server. I used that as a base date for
calculating the Julian date. 2361330.5 is the Julian date for midnight
on 1753-01-01.
In Sree's case 2415021 is the Julian date for 12 noon on 1900-01-01.
Sree's calculation ignores the time portion of the current time and
returns the Julian day number for 12 noon on that day.
As I explained in my correction, Julian dates refer to UTC (GMT) time
so it's not really correct to use GETDATE() or CURRENT_TIMESTAMP as the
input to this calculation. You can use GETUTCDATE instead. In practice
if you are only interested in the integer day number then whether you
choose to base it on local time or UTC may depend on your requirements
and on how the value is to be used.
Hope this helps.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Its the Julian Date equivalent to "1900-01-01 00:00:00.000".
Julian date is the number of days that have elapsed since "1900-01-01
00:00:00.000". So the logic here used is simple, add Julian equivalent of
"1900-01-01 00:00:00.000" ie "2415021" to number of days from "1900-01-01
00:00:00.000" to current data(getdate()).
Hope you got the idea.
Thanks,
Sree
"Madhivanan" wrote:

> Thans David,
> You used the value 17530101. Is this the number of days between January
> 1, 4713 BC and January 1, 1900?
> Sree,
> What does the value 2415021 refer to?
> Madhivanan
>|||Thanks David and Sree
How did you calculate the value 2361330.5 for the date 1753-01-01?
Madhivanan|||Madhivanan wrote:
> Thanks David and Sree
> How did you calculate the value 2361330.5 for the date 1753-01-01?
> Madhivanan
http://aa.usno.navy.mil/data/docs/JulianDate.html
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

convert Julian Date to Calendar Date

How do I convert Julian Date to Calendar Date in T-SQL / DTS based on
following guideline found at Internet?
To convert Julian date to Gregorian date:
double JD = 2299160.5;
double Z = Math.Floor(JD+0.5);
double W = Math.Floor((Z - 1867216.25)/36524.25);
double X = Math.Floor(W/4);
double AA = Math.Floor(Z+1+W-X);
double BB = Math.Floor(AA+1524);
double CC = Math.Floor((BB-122.1)/365.25);
double DD = Math.Floor(365.25*CC);
double EE = Math.Floor((BB-DD)/30.6001);
double FF = Math.Floor(30.6001*EE);
double Day = BB-DD-FF;
double Month;
double Year;
if((EE-13) <= 12 && (EE-13) > 0)
Month = EE-13;
else
Month = EE-1;
if(Month == 1 || Month == 2)
Year = CC-4715;
else
Year = CC-4716;Sam
declare @.julian char(8)
select @.julian = '1996.031'
declare @.dt datetime
select @.dt =
DATEADD(dd,convert(int,right(@.julian,3))
-1,convert(datetime,substring(@.juli
an,1,4)+'0101',212))
select @.dt
"Sam" <cybersam88@.hotmail.com> wrote in message
news:uTQSoztSFHA.2336@.TK2MSFTNGP12.phx.gbl...
> How do I convert Julian Date to Calendar Date in T-SQL / DTS based on
> following guideline found at Internet?
> To convert Julian date to Gregorian date:
> double JD = 2299160.5;
> double Z = Math.Floor(JD+0.5);
> double W = Math.Floor((Z - 1867216.25)/36524.25);
> double X = Math.Floor(W/4);
> double AA = Math.Floor(Z+1+W-X);
> double BB = Math.Floor(AA+1524);
> double CC = Math.Floor((BB-122.1)/365.25);
> double DD = Math.Floor(365.25*CC);
> double EE = Math.Floor((BB-DD)/30.6001);
> double FF = Math.Floor(30.6001*EE);
> double Day = BB-DD-FF;
> double Month;
> double Year;
> if((EE-13) <= 12 && (EE-13) > 0)
> Month = EE-13;
> else
> Month = EE-1;
> if(Month == 1 || Month == 2)
> Year = CC-4715;
> else
> Year = CC-4716;
>|||Well, if you really want to use that formula, then just assign a bunch of
values, along the lines of:
declare @.JD float,
@.Z float,
@.W float,
@.X float,
@.AA float,
@.BB float,
@.CC float,
@.DD float,
@.EE float,
@.FF float
set @.JD = 2299160.5
set @.Z = @.JD+0.5
set @.W = (@.Z - 1867216.25)/36524.25
set @.X = @.W/4.0
set @.AA = @.Z+1+@.W-@.X
set @.BB = @.AA+1524
set @.CC = (@.BB-122.1)/365.25
set @.DD = 365.25*@.CC
set @.EE = (@.BB-@.DD)/30.6001
set @.FF = 30.6001*@.EE
declare @.day float, @.month float, @.year float
set @.Day = @.BB-@.DD-@.FF
if ((@.EE-13) <= 12) and ((@.EE-13) > 0)
set @.Month = @.EE-13
else
set @.Month = @.EE-1
if(@.Month = 1 OR @.Month = 2)
set @.Year = @.CC-4715
else
set @.Year = @.CC-4716
select @.day, @.month, @.year
But I'm not sure that's right. I think a Julian date is just the number of
days since some date about 4000 BC... and that you do it like this:
declare @.mydate datetime
set @.mydate = dateadd(dd, @.JD-2451545, '1-jan-2000')
But this is assuming you just have an integer for your number. If it's a
float, then you will also want to do something like:
select dateadd(second,86400 * (@.JD - convert(bigint,@.JD)),@.mydate)
Which just adds on the fraction to the number of seconds.
Hope this helps,
Rob|||How about convert VB Script to T-SQL?
CalendarDate = Date(1900+INT(JulianDate/1000),1,MOD(JulianDate,1000))
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:O8TdBbuSFHA.3980@.TK2MSFTNGP12.phx.gbl...
> Sam
> declare @.julian char(8)
> select @.julian = '1996.031'
> declare @.dt datetime
> select @.dt =
> DATEADD(dd,convert(int,right(@.julian,3))
-1,convert(datetime,substring(@.ju
li
> an,1,4)+'0101',212))
> select @.dt
>
>
> "Sam" <cybersam88@.hotmail.com> wrote in message
> news:uTQSoztSFHA.2336@.TK2MSFTNGP12.phx.gbl...
>|||Sam
declare @.julian char(8)
select @.julian = REPLACE('1996.031','.','')
SELECT CAST(CAST(@.julian /1000 AS CHAR(4))+'01'+CAST(@.julian %1000 AS
CHAR(2))AS DATETIME)
"Sam" <cybersam88@.hotmail.com> wrote in message
news:elKd5AwSFHA.3244@.TK2MSFTNGP15.phx.gbl...
> How about convert VB Script to T-SQL?
> CalendarDate = Date(1900+INT(JulianDate/1000),1,MOD(JulianDate,1000))
>
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:O8TdBbuSFHA.3980@.TK2MSFTNGP12.phx.gbl...
DATEADD(dd,convert(int,right(@.julian,3))
- 1,convert(datetime,substring(@.juli[color
=darkred]
>