SELECT RIGHT(‘000’ + CAST(DATEPART(dy, AS varchar(3)),3)Ĭoncatenating the YY and the DDD, we now have a TO_JULIAN function. We also need to pad the DDD with zeroes – which I’ll do by putting three zeroes in front of the number and getting the three rightmost characters. SELECT RIGHT(CAST(DATEPART(yy, AS char(4)),2) SELECT DATEPART(yy, DATEPART(dy, to make sure that we have the year in two-digits only, we should convert this to a string and get the rightmost two characters. Using the date type in SQL 2008 would work just the same. I’m going to use as a variable here, of type datetime. YY for the year, and DY for the day of the year. Using the DATEPART function can get each part. So we’re wanting to express a date as YYDDD, where YY is the two-digit form of the year, and DDD is the number of days since Dec 31st of the previous year (ie, the DDDth day of the year). SQL Server doesn’t have a TO_JULIAN function, but we can make one easily enough. People have differing opinions about what Julian means, but the one I got asked about most recently meant YYDDD, as often used by mainframe systems (I think this is Julian Date, as opposed to Julian Day which is the number of days since 4713BC). I often get asked how to convert a datetime into Julian Date format in T-SQL.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |