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
I thought you meant the Julian Date as in the astronomical usage for a while... hence confusion at the number being so low.
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
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?
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.
18 People are following this question.
