convert to T-SQL

Can anybody convert to T-SQL;
select distinct to_char(sysdate,'FMDAY, MONTH DD, YYYY HH:FMMIAM') TODAY
, d_aaa_insthdg CNTRHD
, decode(trunc((d_vst_btcdte - nvl(n_per_birth,d_vst_btcdte))/10000,0),0,'no DOB',
14,'14-16',15,'14-16',16,'14-16',
17,'17-20',18,'17-20',19,'17-20',20,'17-20',
21,'21-25',22,'21-25',23,'21-25',24,'21-25',25,'21-25',
26,'26-30',27,'26-30',28,'26-30',29,'26-30',30,'26-30',
31,'31-35',32,'31-35',33,'31-35',34,'31-35',35,'31-35',
36,'36-40',37,'36-40',38,'36-40',39,'36-40',40,'36-40',
41,'41-45',42,'41-45',43,'41-45',44,'41-45',45,'41-45',
46,'46-50',47,'46-50',48,'46-50',49,'46-50',50,'46-50',
51,'51-55',52,'51-55',53,'51-55',54,'51-55',55,'51-55',
56,'56-60',57,'56-60',58,'56-60',59,'56-60',60,'56-60',
61,'61-65',62,'61-65',63,'61-65',64,'61-65',65,'61-65',
66,'66-70',67,'66-70',68,'66-70',69,'66-70',70,'66-70',
'71+') AGE
, sum(decode(d_vst_dontyp,'WB',1,0)) DRAW1
, sum(decode(d_vst_dontyp,'xx',1,1)) TOTAL0Didn't I already do this for you?

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
--