Count the number of days in a date

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Count the number of days in a date

Author

Topic

Exir
Posting Yak Master

151 Posts

Posted - 2009-05-05 : 04:36:14

I have written this function to calculate the number of days in a date but i dont know how should i check it to see if it returns the true value or not. could you please check it to see if it has any problem or not?
thank you very much


set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


ALTER FUNCTION [dbo].[CountDays] (@day int,@month int,@year int)  
RETURNS INT
AS  
BEGIN 

    declare @y as int
    declare @m as int
    declare @result as int
    
    SET @year=@year-1
    SET @month=@month-1 
    SET @y=@year*365+(@year/4)
    IF @month<=5 
     begin
     SET @m=@month*31
     END
    ELSE
    IF @month<=11 and 5<@month
     begin
     SET @m=6*31+(@month-6)*30
     end
SET @result=@y+@m+@day
	RETURN @result

END

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-05-05 : 04:41:05

It returns 733057 for @day=1,@month=1 and @year=2008
What does it represent?

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-05 : 04:43:19

"Number of days in a date"?
You mean number of days in a year? Or number of days from January 1st to a particulat date?

Use DATEPART

SELECT DATEPART(DAYOFYEAR, getdate())

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-05 : 04:45:44

I think it is supposed to mean number of days since January 1st, 0001.

E 12°55'05.63"
N 56°04'39.26"

Exir
Posting Yak Master

151 Posts

Posted - 2009-05-05 : 05:44:24

yes number of the days since1/1/1.
you know why i wrote it? becouse i had to compare two dates. for example i want to see th records between date1 and date2 and becouse my date was not in english date(christ birthday) i couldnt use datetime type and my date was saved in nvarchar. so for comparing them i wrote this function. is this true?

dalibor
Starting Member

21 Posts

Posted - 2009-05-05 : 05:48:42

Hi,

your resolution is too complicated.
See funcion last_day.
Returned last day of month.

SELECT SYSDATE,
LAST_DAY(SYSDATE) "Last",
LAST_DAY(SYSDATE) - SYSDATE "Days Left"
FROM DUAL;

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-05-05 : 06:28:40

quote:
Originally posted by dalibor

Hi,

your resolution is too complicated.
See funcion last_day.
Returned last day of month.

SELECT SYSDATE,
LAST_DAY(SYSDATE) "Last",
LAST_DAY(SYSDATE) - SYSDATE "Days Left"
FROM DUAL;

This is MS SQL Server forum and your solution will work in Oracle

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-05-05 : 06:47:41

One possible method I think is

declare @day int,@month int,@year int, @proper_date datetime
select @day=1, @month=1,@year =2008

select @proper_date=dateadd(month,@month-1,dateadd(year,@year-1900,0))+@day-1
select datediff(day,0,dateadd(year,1899,@proper_date))

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources