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.
Author |
Topic |
ramya888
Starting Member
11 Posts |
Posted - 2013-06-17 : 08:54:17
|
i am having two dates09-apr-2013 to 09-mar=2013in april 21daysin march 9dayshow 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 meRamy |
|
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 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
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=2013in april 21daysin march 9days--Chandu |
|
|
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=2013in april 21daysin march 9days--Chandu
thats just one business case there's no necessity that it should fall within a month range alwaysfor ex.,there are long leave options like sabbatical,maternity etc so it may span multiple months..------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
ramya888
Starting Member
11 Posts |
Posted - 2013-06-18 : 00:55:05
|
this query is correctDECLARE @date1 DATE = '09-04-2013', @date2 DATE = '09-05-2013'SELECT DATEDIFF ( DD, DATEADD(MONTH, DATEDIFF(MONTH, 0, @date2), 0), @date2) +1SELECT DATEDIFF(DD, @date1, DATEADD(DD, -DATEPART(DD, @date1), DATEADD( MONTH, 1 , @date1)))Ramy |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-06-18 : 00:57:34
|
quote: Originally posted by ramya888 this query is correctDECLARE @date1 DATE = '09-04-2013', @date2 DATE = '09-05-2013'SELECT DATEDIFF ( DD, DATEADD(MONTH, DATEDIFF(MONTH, 0, @date2), 0), @date2) +1SELECT DATEDIFF(DD, @date1, DATEADD(DD, -DATEPART(DD, @date1), DATEADD( MONTH, 1 , @date1)))Ramy
thank you Have you seen Visakh's solution?--Chandu |
|
|
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 NumberOfDaysunion allselect DATENAME(mm,DateFiledTwo) AS MonthName, DATEDIFF(DD,(convert(varchar,dateadd(d,-day(DateFiledTwo),dateadd(m,1,DateFiledTwo)),106)),DateFiledTwo)JanakiRam |
|
|
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 NumberOfDaysunion allselect 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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
JanakiRam
Starting Member
22 Posts |
Posted - 2013-06-18 : 02:22:29
|
Ya i saw your solution and understood the limitation of my approachJanakiRam |
|
|
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 approachJanakiRam
Good------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
|
|
|