question

adiamr avatar image
adiamr asked

Convert datetime column to julian date

Hi I have a datetime column loadstartdate which I want to convert to Julian date and store in variable , any suggestions?Using sql server
datetimeconvertcast-convert
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

For example loadstartdate : 2016-03-25 16:13:53.033 convert to Julian date as 16089. Any suggestions would be very helpful
0 Likes 0 ·
I thought you meant the Julian Date as in the astronomical usage for a while... hence confusion at the number being so low.
0 Likes 0 ·

1 Answer

· Write an Answer
KenJ avatar image
KenJ answered
I might use datepart to get the year then datepart again to get the day of year then concatenate those results after converting to character data types and accounting for leading zeroes. Something like this (I get 16085 rather than 16089 for 25 March 2016) declare @sample_date datetime = '2016-03-25 16:13:53.033' ; select right('00' + convert(varchar, datepart(YY, @sample_date)), 2) + right('000' + convert(varchar, datepart(DAYOFYEAR, @sample_date)), 3) as julian_date ; julian_date ----------- 16085
4 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thanks ken , but I need the exact Julian date that I am pulling from another column name for comparison. So the datetime I can't specify as it needs to convert for everyday , every month and year. Is there a way I can set the variable to datetime column and then do the Julian date conversion?
0 Likes 0 ·
The query does what you ask - notice the select statement does not have any specific month or year so it works for any month and year combination. You provided the hard coded date so I used that to show how the query works. I thought you would realize it was just for illustration. Every place in the SELECT query that the variable name appears is where you place your column name. Don't forget a FROM clause that references the table that contains the date column.
0 Likes 0 ·
Thank you very much ken...it worked like a charm. Sorry was too quick to send a reply yesterday.
0 Likes 0 ·
Glad to help
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.