August 3, 2004 at 8:17 am
I am trying to find the date that I can only derive from the day of year eg. 61
I also have the year which is 2004
can this be done?
I have tried different datepart queries but with no avail
Please help
August 3, 2004 at 8:37 am
select dateadd(d, 61, '2004-01-01')
Above should work.
August 3, 2004 at 8:38 am
How bout something like
DECLARE @Days INTEGER
DECLARE @Year CHAR(4)
SET @Days = 61
SET @Year = (SELECT CAST(DATEPART(YEAR, GETDATE()) AS CHAR(4)))
SELECT DATEADD(DAY, @Days, '01/01/' + @Year)
Good Hunting!
AJ Ahrens
webmaster@kritter.net
August 3, 2004 at 8:38 am
use dateadd, if it's the 61st day of the year add 60 days to jan 1st
DATEADD (d ,(61-1),'01/01/2004' )
MVDBA
August 3, 2004 at 10:13 am
I thought this seemed interesting and that functions to convert between julian and gregorian might be useful.
So, here goes -
-- Steve
/*
Usage examples -
DECLARE
@Date datetime,
@Julian int
SET @Date = '2004-08-03'
SELECT @Julian = dbo.getJulian(@Date)
SELECT @Julian
SELECT dbo.getGregorian(@Julian)
*/
CREATE FUNCTION getJulian (@Date datetime)
RETURNS INT
AS
BEGIN
DECLARE @Julian int
SELECT @Julian = CAST(CAST(DATEPART(YEAR, @Date)AS char(4)) + '000' AS int)
+ DATEPART(dayofyear, @Date)
RETURN(@Julian)
END
GO
CREATE FUNCTION getGregorian (@Julian int)
RETURNS DATETIME
AS
BEGIN
DECLARE @Date datetime
SELECT @Date = DATEADD(DAY, substring(cast(@Julian as char(7)),5,3) - 1, '01/01/' + substring(cast(@Julian as char(7)),1,4))
RETURN(@Date)
END
GO
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply