This might be enough to get you where you need to go.
I do a report where I show the machines with warranty expiring in the next
@DAYSLEFT. This is used to show machines expiring in the next couple of weeks
so we can go out and inspect those systems to make sure there is nothing wrong
with them before their warranty expires. If you don't want to prompt for the
number of days, you can just replace @DAYSLEFT with an integer number of days
to report on. The complicatedness of the CASE is to ignore machines with no
warranty date information (I think.)
This is what I use in my "WHERE" clause.
AND CASE
WHEN DATEDIFF (dd,GETDATE(),CASE WHEN
v_GS_Warranty_Info.EndDate0 like '%/%/%' then V_GS_Warranty_Info.EndDate0 ELSE
GETDATE() END ) < 0 THEN '0'
ELSE DATEDIFF (dd,GETDATE(),CASE WHEN
v_GS_Warranty_Info.EndDate0 like '%/%/%' then V_GS_Warranty_Info.EndDate0 ELSE
GETDATE() END )
END BETWEEN 1 and @DAYSLEFT
Also, here is something you can use in your SELECT part of the report to show a
column of how many days are left in the warranty
v_GS_Warranty_Info.EndDate0 as 'Warranty Ends',
CASE
WHEN DATEDIFF (dd,GETDATE(),CASE WHEN
v_GS_Warranty_Info.EndDate0 like '%/%/%' then V_GS_Warranty_Info.EndDate0 ELSE
GETDATE() END ) < 0 THEN '0'
ELSE DATEDIFF (dd,GETDATE(),CASE WHEN
v_GS_Warranty_Info.EndDate0 like '%/%/%' then V_GS_Warranty_Info.EndDate0 ELSE
GETDATE() END )
END
as 'Days Left',
From: [email protected] [mailto:[email protected]] On
Behalf Of Marcum, John
Sent: Monday, February 10, 2014 7:51 AM
To: SMS List ([email protected])
Subject: [mssms] TSQL Calculate Date
How do I calculate the number of days between the current date and a future
date that's in a column?
For instance, I'd like to calcultate the number of days the current date and
the war.EndDate0 in this query.
Select
war.EndDate0 as 'Warranty End Date'
from v_GS_CUSTOM_WARRANTY_INFO_2_00 war
________________________________
John Marcum
Lead Desktop Engineer
Bradley Arant Boult Cummings LLP
________________________________
________________________________
Confidentiality Notice: This e-mail is from a law firm and may be protected by
the attorney-client or work product privileges. If you have received this
message in error, please notify the sender by replying to this e-mail and then
delete it from your computer.
________________________________
Notice: This UI Health Care e-mail (including attachments) is covered by the
Electronic Communications Privacy Act, 18 U.S.C. 2510-2521, is confidential and
may be legally privileged. If you are not the intended recipient, you are
hereby notified that any retention, dissemination, distribution, or copying of
this communication is strictly prohibited. Please reply to the sender that you
have received the message in error, then delete it. Thank you.
________________________________
