SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 how to split the days between two dates
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ramya888
Starting Member

India
11 Posts

Posted - 06/17/2013 :  08:54:17  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
2224 Posts

Posted - 06/17/2013 :  09:16:23  Show Profile  Reply with Quote
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

India
52325 Posts

Posted - 06/17/2013 :  09:20:14  Show Profile  Reply with Quote

SELECT MONTH([Date]), COUNT(*)
FROM dbo.CalendarTable('20130301','20130409',0,0)
GROUP BY MONTH([Date])


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

India
52325 Posts

Posted - 06/17/2013 :  09:33:32  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
2224 Posts

Posted - 06/17/2013 :  10:03:22  Show Profile  Reply with Quote
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

India
52325 Posts

Posted - 06/17/2013 :  10:06:31  Show Profile  Reply with Quote
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

India
11 Posts

Posted - 06/18/2013 :  00:55:05  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
2224 Posts

Posted - 06/18/2013 :  00:57:34  Show Profile  Reply with Quote
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

India
22 Posts

Posted - 06/18/2013 :  01:51:33  Show Profile  Reply with Quote
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

India
52325 Posts

Posted - 06/18/2013 :  02:13:50  Show Profile  Reply with Quote
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

India
22 Posts

Posted - 06/18/2013 :  02:22:29  Show Profile  Reply with Quote
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

India
52325 Posts

Posted - 06/18/2013 :  02:27:25  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000