Wednesday, August 5, 2009

SQL: Georgian date to Julian date

Hi...

Here is the function to convert Georgian date to Julian date. Today it was useful when I was writing a application to transfer data to ERP system.


Syntax:

CREATE FUNCTION dbo.to_julian(@date datetime) RETURNS char(6) AS

BEGIN

RETURN (SELECT '1'+ RIGHT(CAST(YEAR(@date) AS CHAR(4)),2) + RIGHT('000' + CAST(DATEPART(dy, @date) AS varchar(3)),3))

END


Execute:

SELECT dbo.to_julian(getdate())




The output is 109217 for today(2009-08-05) Where 1-> 21st century, 09 -> Current Year & 217 -> Is the number of days since the start of this year.


Regards

Fauzi

No comments: