?
Solved

T-SQL Date to Julian Conversion

Posted on 2003-11-25
6
Medium Priority
?
1,382 Views
Last Modified: 2009-01-27
Using T-SQL how can a date field (mm/dd/yyyy) be converted to a Julian Day Number?
0
Comment
Question by:jlindeman
4 Comments
 
LVL 5

Accepted Solution

by:
snimmaga earned 256 total points
ID: 9819360
0
 
LVL 6

Assisted Solution

by:lausz
lausz earned 248 total points
ID: 9819479
0
 
LVL 70

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 248 total points
ID: 9820067
Try this:

DECLARE @date SMALLDATETIME
DECLARE @julDate INT
SET @date = '2003-11-25'

SET @julDate = YEAR(@date) * 1000 +
      DATEDIFF(DAY, CAST(YEAR(@date) AS CHAR(4)) + '-01-01', @date) + 1

SELECT @julDate


Didn't know if you wanted if YYDDD or YYYYDDD, so used YYYY.
0
 
LVL 50

Assisted Solution

by:Lowfatspread
Lowfatspread earned 248 total points
ID: 9820590
Hi jlindeman,

Declare @jd int
set @Jd = Year(@date) * 1000 + Datepart(dy,@date)



Cheers!
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

601 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question