how to know how many day there is between two date !?

Question

Post reply

how to know how many day there is between two date !?

christophe.bernard-680841

Say Hey Kid

Points: 700
More actions
June 16, 2009 at 6:31 am

#131248

hi,
i have two date in sql server 2005
1/12/2008 + hours
31/01/2009 + hours
i'd like in a query sql to know the number of day in year 2009 and the number of day total !
Someone can help me, i'm a beginner !
Have a nice day
Christophe

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

ss-457805 SSCertifiable Points: 5872 More actions · Answer 1

You can use datediff to get the number of days;

select datediff(dd,'2001-01-01 00:00:00','2001-12-31 00:00:00')

blog: http://sarveshsingh.com Twitter: @sarveshsing

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

Could you explain your problem in a little more detail and perhaps show us what you are looking for with a couple of examples?

Your initial post is a little vaque as to what you are looking for.

christophe.bernard-680841 Say Hey Kid Points: 700 More actions · Answer 3

Hi all,

i'm a beginner in t sql and i have a table with some field, text and date

i have in one row two date

dateStart and dateEnd

22/10/2008 19/01/2009

and IN ONE query i'd like to have :

- the total of day BETWEEN the two date

- the total day for dateEnd at begin 1/01/2009 because we are in 2009

Thanks for your time

Christ

Mike_D Hall of Fame Points: 3295 More actions · Answer 4

I'm assuming the second requirement is for the day number of the EndDate relative to the current year as in a Julian date. Therefore try:

SELECTDATEDIFF(DD,dateStart,DateEnd) AS DaysDiff,

DATEDIFF(DD,CONVERT(datetime,CONVERT(char(4),YEAR(dateEnd)) + '0101'),dateEnd) + 1AS DayOfYear

FROM.........

Mike

FreeHansje SSChampion Points: 11751 More actions · Answer 5

I believe you might appreciate this article:

Greetz,
Hans Brouwer

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

Is this what you are looking for?

--dateStart and dateEnd

--22/10/2008 19/01/2009

--

--and IN ONE query i'd like to have :

--

--- the total of day BETWEEN the two date

--- the total day for dateEnd at begin 1/01/2009 because we are in 2009

create table #TestTab (

RecID int identity(1,1),

DateStart datetime,

DateEnd datetime);

insert into #TestTab (DateStart, DateEnd)

select '2008-10-22', '2009-01-19' union all

select '2008-12-31', '2009-02-01' union all

select '2009-01-01', '2009-01-01';

select

DateStart,

DateEnd,

datediff(dd, DateStart, DateEnd) + 1 as TotalDays, -- assumes 1 if dates are the same

datediff(dd, dateadd(yy, datediff(yy, 0, DateEnd), 0), DateEnd) + 1 as DaysInCurrentYear -- same as above

from

#TestTab;

drop table #TestTab;

christophe.bernard-680841 Say Hey Kid Points: 700 More actions · Answer 7

Hi all,

thanks for your time, that's excatly what i want !

thanks for all

Christophe