I got a requirement to display the date which is the format 7-digit YYYYDDD (Julian Date) to display in Gregarian format.
This is the query I wrote to convert YYYYDDD format to YYYY-MM-DD format.
YYYY - 4 - digit Year
DDD - number of the day in the year
SQL Query:
SELECT [Julian Date], DATEADD(DAY, [Julian Date]% 1000 - 1, DATEADD(YEAR,
[Julian Date]/ 1000 - 1900, 0)) AS [Gregarian Date]
FROM dbo.Test
Sample Output:
how to we validate if given integer is valid Julian date.
ReplyDeleteHi Anonymous,
ReplyDeleteI can suggest you several validations.
1)Make sure the date length is 7
2)Take the last three characters of the date value and check whether the value >= 001 and <= 365
3)Also Values for datetime data types earlier than January 1, 1753 are not permitted in SQL Server. So check the year by taking first 4 characters.
I hope it will help you
This algorithm doesn't work after Feb 28 during leap years!
ReplyDeleteInstead, try this:
dateadd(d,convert(int,right(JulianDate,3))-1,convert(date,left(JulianDate, 4) + '-01-01'))
Basically, get the year and convert to Jan 1 of that year, then add the number of days minus one.
Scott Cameron
scottca@live.com
The above method fails with error "Conversion failed when converting date and/or time from character string." when I run against a large set of data with Julian date that needs to be converted.
ReplyDeleteSomehow it executes when I run it against a smaller sample of data. Anyone encountered such problem? Thanks in advance
hello sir I saw your blog and i think you have much idea about .net sql etc.sir i am a independent project developer and i need your help.it will be grateful if you will help me.
ReplyDeletei am waiting for your reply.
my mail id sssatpathy191@gmail.com.
thank you sir.
how show JD# in display?
ReplyDelete