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
 General SQL Server Forums
 New to SQL Server Programming
 Help with calculating between two dates

Author  Topic 

wembleybear
Yak Posting Veteran

93 Posts
Posted - 2013-05-31 : 06:04:50
I'm looking for some help calculating between two dates using DATEDIFF but I have a two-part problem with it.

The dates should be calculated between orig_start_rent and stop_rent in the code below, the problem is that sometimes the stop_rent is NULL as the hire is ongoing. In this situation I need the stop_rent to default to the current date.

The other part of the problem is that Saturday & Sunday should not be counted.

The code of my query is below, any help would be appreciated as I'm still new to SQL:


Select h.acct, h.dticket, h.po_number, Max(h.sname) sname, h.szip, i.item, n.serial, n.cat, n.sub, Max(i.quant) quant, Max(i.weekchg) weekchg, Max(i.descr) descr, i.stop_rent, business_classes.name, i.orig_start_rent, c.custname

From deltickhdr h Inner Join
deltickitem i On h.dticket = i.dticket Inner Join
inventory n On i.item = n.item Inner Join
customers c On h.acct = c.custnum And c.type = N'C' Inner Join
itype On i.itype = itype.itype Inner Join
business_classes business_classes On business_classes.id=c.business_class_id

Where i.start_rent Is Not Null And h.posted <> 0 And i.quant <> 0 And
itype.logictype Not In (2, 3) And i.descr Not Like '%Appello%'

Group By h.acct, h.dticket, h.po_number, h.szip, i.item, n.serial, n.cat, n.sub,
i.stop_rent, business_classes.name, i.orig_start_rent, c.custname, i.ucounter, h.sname

Order By h.dticket, i.ucounter


Many thanks
Martyn

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts
Posted - 2013-05-31 : 06:32:33
DateDiff function takes three (input) partameters .. for syntax and usage take a look at http://msdn.microsoft.com/en-us/library/ms189794.aspx

And for to not include saturday and sunday, check this
DATEPART(weekday,<Date field/value>) NOT IN (1,7) --if you're providing an explicit date value then it should be in date format and enclosed in single qoutes '2013-05-31'




Cheers
MIK
Go to Top of Page

wembleybear
Yak Posting Veteran

93 Posts
Posted - 2013-05-31 : 07:26:47
Thank you - I've simplified my code to make it easier to see what my primary problem is. The code now gives me the result I want when there are 2 dates to compare, but when the stop_rent date is NULL, it returns the NULL as the DaysHire amount. As the item is still on hire in this case, I want the DaysHire to work it out as if the stop_rent is the current date. How can I do this - this was the first part of my original question?


select dticket, orig_start_rent, stop_rent,

(DATEDIFF(dd, orig_start_rent, stop_rent))
-(DATEDIFF(wk, orig_start_rent, stop_rent) * 2)
-(CASE WHEN DATENAME(dw, orig_start_rent) = 'Sunday' THEN 1 ELSE 0 END)
-(CASE WHEN DATENAME(dw, stop_rent) = 'Saturday' THEN 1 ELSE 0 END)
as DaysHire

from deltickitem

Many thanks
Martyn
Go to Top of Page

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts
Posted - 2013-05-31 : 10:41:41
Use ISNULL or Coalesce function for null values of Stop_Rent and to replace them with Getdate() :)

Cheers
MIK
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2013-06-01 : 05:37:35
quote:
Originally posted by wembleybear

Thank you - I've simplified my code to make it easier to see what my primary problem is. The code now gives me the result I want when there are 2 dates to compare, but when the stop_rent date is NULL, it returns the NULL as the DaysHire amount. As the item is still on hire in this case, I want the DaysHire to work it out as if the stop_rent is the current date. How can I do this - this was the first part of my original question?


select dticket, orig_start_rent, stop_rent,

(DATEDIFF(dd, orig_start_rent, stop_rent))
-(DATEDIFF(wk, orig_start_rent, stop_rent) * 2)
-(CASE WHEN DATENAME(dw, orig_start_rent) = 'Sunday' THEN 1 ELSE 0 END)
-(CASE WHEN DATENAME(dw, stop_rent) = 'Saturday' THEN 1 ELSE 0 END)
as DaysHire

from deltickitem

Many thanks
Martyn

the above code has a dependency on server language settings
In case you want the code to be ported to different server with different language settings it may not work
I prefer writing language independent logic which is easily portable

see
http://visakhm.blogspot.in/2012/08/creating-server-independent-day.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-01 : 20:14:30
quote:
Originally posted by MIK_2008

DateDiff function takes three (input) partameters .. for syntax and usage take a look at http://msdn.microsoft.com/en-us/library/ms189794.aspx

And for to not include saturday and sunday, check this
DATEPART(weekday,<Date field/value>) NOT IN (1,7) --if you're providing an explicit date value then it should be in date format and enclosed in single qoutes '2013-05-31'




Cheers
MIK

Still this has dependency on DATEFIRST setting of the server

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

wembleybear
Yak Posting Veteran

93 Posts
Posted - 2013-06-03 : 04:46:36
quote:
Originally posted by MIK_2008

Use ISNULL or Coalesce function for null values of Stop_Rent and to replace them with Getdate() :)

Cheers
MIK


Excellent, thanks MIK

For this purpose it works perfectly with ISNULL. Once again v. useful info.


Kind regards
Martyn
Go to Top of Page

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts
Posted - 2013-06-03 : 07:53:50
quote:
Originally posted by visakh16

quote:
Originally posted by MIK_2008

DateDiff function takes three (input) partameters .. for syntax and usage take a look at http://msdn.microsoft.com/en-us/library/ms189794.aspx

And for to not include saturday and sunday, check this
DATEPART(weekday,<Date field/value>) NOT IN (1,7) --if you're providing an explicit date value then it should be in date format and enclosed in single qoutes '2013-05-31'




Cheers
MIK

Still this has dependency on DATEFIRST setting of the server

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs



Correct, thanks for sharing the info :)

So, one can set its to his/her own choice via
SET DateFirst <number> --number should be in between 1 and 7.

this change would be for a specific session

Cheers
MIK
Go to Top of Page

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts
Posted - 2013-06-03 : 07:55:41
quote:
Originally posted by wembleybear

quote:
Originally posted by MIK_2008

Use ISNULL or Coalesce function for null values of Stop_Rent and to replace them with Getdate() :)

Cheers
MIK


Excellent, thanks MIK

For this purpose it works perfectly with ISNULL. Once again v. useful info.


Kind regards
Martyn


You're welcome!

Cheers
MIK
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2013-06-03 : 23:54:21
quote:
Originally posted by MIK_2008

quote:
Originally posted by visakh16

quote:
Originally posted by MIK_2008

DateDiff function takes three (input) partameters .. for syntax and usage take a look at http://msdn.microsoft.com/en-us/library/ms189794.aspx

And for to not include saturday and sunday, check this
DATEPART(weekday,<Date field/value>) NOT IN (1,7) --if you're providing an explicit date value then it should be in date format and enclosed in single qoutes '2013-05-31'




Cheers
MIK

Still this has dependency on DATEFIRST setting of the server

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs



Correct, thanks for sharing the info :)

So, one can set its to his/her own choice via
SET DateFirst <number> --number should be in between 1 and 7.

this change would be for a specific session

Cheers
MIK

yep depending on their interpretation of the week.

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site