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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 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
2242 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
52326 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
52326 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
2242 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
52326 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
2242 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
52326 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
52326 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  
 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.07 seconds. Powered By: Snitz Forums 2000