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 2008 Forums
 Transact-SQL (2008)
 how to split the days between two dates

Author  Topic 

ramya888
Starting Member

11 Posts

Posted - 2013-06-17 : 08:54:17
i am having two dates

09-apr-2013 to 09-mar=2013

in april 21days
in march 9days

how to show like this ,if the employee is taking a leave from 09-apr-2013 to 09-mar-2013 ,i want to write the query to split the days between two days.pls help me

Ramy

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-06-17 : 09:16:23
DECLARE @date1 DATE = '09-04-2013', @date2 DATE = '09-05-2013'
SELECT DATEDIFF ( DD, DATEADD(MONTH, DATEDIFF(MONTH, 0, @date2), 0), @date2) +1
SELECT DATEDIFF(DD, @date1, DATEADD(DD, -DATEPART(DD, @date1), DATEADD( MONTH, 1 , @date1)))


--
Chandu
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-17 : 09:20:14
[code]
SELECT MONTH([Date]), COUNT(*)
FROM dbo.CalendarTable('20130301','20130409',0,0)
GROUP BY MONTH([Date])
[/code]

see CalendarTable function here

http://visakhm.blogspot.in/2010/02/generating-calendar-table.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-17 : 09:33:32
quote:
Originally posted by bandi

DECLARE @date1 DATE = '09-04-2013', @date2 DATE = '09-05-2013'
SELECT DATEDIFF ( DD, DATEADD(MONTH, DATEDIFF(MONTH, 0, @date2), 0), @date2) +1
SELECT DATEDIFF(DD, @date1, DATEADD(DD, -DATEPART(DD, @date1), DATEADD( MONTH, 1 , @date1)))


--
Chandu


Hmm..
what if date is more than 1 month apart?
ie DECLARE @date1 DATE = '09-02-2013', @date2 DATE = '09-05-2013'


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-06-17 : 10:03:22
But OP given second date as previous month visakh...

09-apr-2013 to 09-mar=2013
in april 21days
in march 9days


--
Chandu
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-17 : 10:06:31
quote:
Originally posted by bandi

But OP given second date as previous month visakh...

09-apr-2013 to 09-mar=2013
in april 21days
in march 9days


--
Chandu


thats just one business case
there's no necessity that it should fall within a month range always
for ex.,there are long leave options like sabbatical,maternity etc so it may span multiple months..

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

ramya888
Starting Member

11 Posts

Posted - 2013-06-18 : 00:55:05
this query is correct
DECLARE @date1 DATE = '09-04-2013', @date2 DATE = '09-05-2013'
SELECT DATEDIFF ( DD, DATEADD(MONTH, DATEDIFF(MONTH, 0, @date2), 0), @date2) +1
SELECT DATEDIFF(DD, @date1, DATEADD(DD, -DATEPART(DD, @date1), DATEADD( MONTH, 1 , @date1)))

Ramy
Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-06-18 : 00:57:34
quote:
Originally posted by ramya888

this query is correct
DECLARE @date1 DATE = '09-04-2013', @date2 DATE = '09-05-2013'
SELECT DATEDIFF ( DD, DATEADD(MONTH, DATEDIFF(MONTH, 0, @date2), 0), @date2) +1
SELECT DATEDIFF(DD, @date1, DATEADD(DD, -DATEPART(DD, @date1), DATEADD( MONTH, 1 , @date1)))
Ramy


thank you
Have you seen Visakh's solution?


--
Chandu
Go to Top of Page

JanakiRam
Starting Member

22 Posts

Posted - 2013-06-18 : 01:51:33
select DATENAME(mm,DateFiledOne) AS MonthName,DATEDIFF(DD,(convert(varchar,dateadd(d,-(day(DateFiledOne-1)),DateFiledOne),106)),DateFiledOne) AS NumberOfDays
union all
select DATENAME(mm,DateFiledTwo) AS MonthName, DATEDIFF(DD,(convert(varchar,dateadd(d,-day(DateFiledTwo),dateadd(m,1,DateFiledTwo)),106)),DateFiledTwo)

JanakiRam
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-18 : 02:13:50
quote:
Originally posted by JanakiRam

select DATENAME(mm,DateFiledOne) AS MonthName,DATEDIFF(DD,(convert(varchar,dateadd(d,-(day(DateFiledOne-1)),DateFiledOne),106)),DateFiledOne) AS NumberOfDays
union all
select DATENAME(mm,DateFiledTwo) AS MonthName, DATEDIFF(DD,(convert(varchar,dateadd(d,-day(DateFiledTwo),dateadd(m,1,DateFiledTwo)),106)),DateFiledTwo)

JanakiRam


Hope you saw my solution and understood the limitation of your approach.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

JanakiRam
Starting Member

22 Posts

Posted - 2013-06-18 : 02:22:29
Ya i saw your solution and understood the limitation of my approach

JanakiRam
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-18 : 02:27:25
quote:
Originally posted by JanakiRam

Ya i saw your solution and understood the limitation of my approach

JanakiRam


Good

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
   

- Advertisement -