mikeydk
asked on
SQL Query date / datediff
Hey
I would like to have the following only find the machines installed from today (4/4/2014) and forward
Howto?
I would like to have the following only find the machines installed from today (4/4/2014) and forward
Howto?
select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System inner join SMS_G_System_OPERATING_SYSTEM on SMS_G_System_OPERATING_SYSTEM.ResourceId = SMS_R_System.ResourceId
WHERE dateDiff(dd, SMS_G_System_OPERATING_SYSTEM.InstallDate, GetDate()) < 30
You should use table alias names to improve readability. E.g.
btw, there is no "forward"..
SELECT S.ResourceID ,
S.ResourceType ,
S.Name ,
S.SMSUniqueIdentifier ,
S.ResourceDomainORWorkgroup ,
S.Client
FROM SMS_R_System S
INNER JOIN SMS_G_System_OPERATING_SYSTEM OS ON OS.ResourceId = S.ResourceId
WHERE DATEDIFF(DAY,OS.InstallDate, GETDATE()) = 1;
btw, there is no "forward"..
WHERE
SMS_G_System_OPERATING_SYS TEM.Instal lDate >= CONVERT(char(8), GETDATE(), 112)
SMS_G_System_OPERATING_SYS
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
https://www.experts-exchange.com/Database/Miscellaneous/A_1499-DATE-and-TIME-don%27t-be-scared-and-do-it-right-the-first-time.html
hence, syntax like this:
WHERE dateDiff(dd, SMS_G_System_OPERATING_SYS
should be banned ...
the correct way would be for example:
WHERE SMS_G_System_OPERATING_SYS
now, getdate() contains also a time portion (data type is datetime), so you may want to use the date data type instead: CONVERT(date, GETDATE())
http://technet.microsoft.com/en-us/library/ms188383.aspx
for your query, it would look like this:
WHERE SMS_G_System_OPERATING_SYS