October 2, 2012 at 12:45 pm
I have the statement below, and I am trying to change the date to use November 1st (11/01/2012). Can't seem to figure this out..
I know I need to change 'CURRENT_TIMESTAMP' to something like '2012-11-01' but I've tried many combos can't seem to get it to wrok..
;with cte as (SELECT
'WSI3' AS 'Rec ID',
--E.EecEEID AS 'Emp ID',
LEFT(EecEEID, LEN(EecEEID)-3) AS 'EMP ID', --cut off last three chars to fit mcfinas table
--EecEmpNo as test,
eepNameFirst AS 'First Name',
eepNameLast AS 'Last Name',
--EecDateOfOriginalHire AS 'First Service Date',
EepAddressLine1 AS 'Address 1',
ISNULL(Cast(EepAddressLine2 AS VARCHAR(20)), '') AS 'Address 2',
EepAddressCity AS 'City',
EepAddressState AS 'State',
EepAddressZipCode AS 'Zip',
CmpCompanyCode AS 'Co',
--(select EecOrgLvl2 from empcomp E Join mcfina.dbo.siteinfo mc ON e.EecOrgLvl2 = mc.[branch code]where E.eecEEID = eepEEID) as 'Dept',
EecOrgLvl2 as 'Dept',
CONVERT(NVARCHAR(10),EecDateOfSeniority,120) AS 'Service Date',
dateadd(year,5, EecDateOfSeniority) as [Due Date for 5 years of Service Award],
dateadd(year,10, EecDateOfSeniority) as [Due Date for 10 years of Service Award],
dateadd(year,15,EecDateOfSeniority) as [Due Date for 15 years of Service Award],
dateadd(year,20,EecDateOfSeniority) as [Due Date for 20 years of Service Award],
dateadd(year,25,EecDateOfSeniority) as [Due Date for 25 years of Service Award],
dateadd(year,30,EecDateOfSeniority) as [Due Date for 30 years of Service Award],
dateadd(year,35,EecDateOfSeniority) as [Due Date for 35 years of Service Award],
dateadd(year,40,EecDateOfSeniority) as [Due Date for 40 years of Service Award],
dateadd(year,45,EecDateOfSeniority) as [Due Date for 45 years of Service Award]
FROM
EmpPers
JOIN EmpComp E
ON E.eecEEID = eepEEID
JOIN Company
ON eecCoID = cmpCoID
WHERE
EecDateOfTermination IS NULL)
--select CURRENT_TIMESTAMP
select *, '20' as [Yrs of Serv]
from cte where [Due Date for 20 years of Service Award] >= dateadd(month, datediff(month,'19000101',CURRENT_TIMESTAMP + 90),'19000101')
AND
[Due Date for 20 years of Service Award] < dateadd(month, 1+ datediff(month,'19000101',2012-11-01 + 90),'19000101')
UNION ALL
select *, '10' as [Yrs of Serv]
from cte where [Due Date for 10 years of Service Award] >= dateadd(month, datediff(month,'19000101',2012-11-01 + 90),'19000101')
AND
[Due Date for 10 years of Service Award] < dateadd(month, 1+ datediff(month,'19000101',2012-11-01 + 90),'19000101')
UNION ALL
select *, '5' as [Yrs of Serv]
from cte where [Due Date for 5 years of Service Award] >= dateadd(month, datediff(month,'19000101',2012-11-01 + 90),'19000101')
AND
[Due Date for 5 years of Service Award] < dateadd(month, 1+ datediff(month,'19000101',2012-11-01 + 90),'19000101')
UNION ALL
select *, '15' as [Yrs of Serv]
from cte where [Due Date for 15 years of Service Award] >= dateadd(month, datediff(month,'19000101',2012-11-01 + 90),'19000101')
AND
[Due Date for 15 years of Service Award] < dateadd(month, 1+ datediff(month,'19000101',2012-11-01 + 90),'19000101')
UNION ALL
select *, '25' as [Yrs of Serv]
from cte where [Due Date for 25 years of Service Award] >= dateadd(month, datediff(month,'19000101',2012-11-01 + 90),'19000101')
AND
[Due Date for 25 years of Service Award] < dateadd(month, 1+ datediff(month,'19000101',2012-11-01 + 90),'19000101')
UNION ALL
select *, '30' as [Yrs of Serv]
from cte where [Due Date for 30 years of Service Award] >= dateadd(month, datediff(month,'19000101',2012-11-01 + 90),'19000101')
AND
[Due Date for 30 years of Service Award] < dateadd(month, 1+ datediff(month,'19000101',2012-11-01 + 90),'19000101')
UNION ALL
select *, '35' as [Yrs of Serv]
from cte where [Due Date for 35 years of Service Award] >= dateadd(month, datediff(month,'19000101',2012-11-01 + 90),'19000101')
AND
[Due Date for 40 years of Service Award] < dateadd(month, 1+ datediff(month,'19000101',2012-11-01 + 90),'19000101')
UNION ALL
select *, '40' as [Yrs of Serv]
from cte where [Due Date for 40 years of Service Award] >= dateadd(month, datediff(month,'19000101',2012-11-01 + 90),'19000101')
AND
[Due Date for 40 years of Service Award] < dateadd(month, 1+ datediff(month,'19000101',2012-11-01 + 90),'19000101')
UNION ALL
select *, '45' as [Yrs of Serv]
from cte where [Due Date for 45 years of Service Award] >= dateadd(month, datediff(month,'19000101',2012-11-01 + 90),'19000101')
AND
[Due Date for 45 years of Service Award] < dateadd(month, 1+ datediff(month,'19000101',2012-11-01 + 90),'19000101')code]
October 2, 2012 at 12:52 pm
If what you are trying to do is get the first day of the following month try this:
DATEADD(month, DATEDIFF(month, 0, getdate()) + 1, '');
Also, you should know that SQL Server implements CURRENT_TIMESTAMP using GETDATE(), so you might as well use GETDATE()...
Roland Alexander
The Monday Morning DBA
There are two means of refuge from the miseries of life: music and cats. ~ Albert Schweitzer
October 2, 2012 at 1:31 pm
Cool...thanks Roland.
October 3, 2012 at 1:22 am
DECLARE @DATE=(SELECT DATEADD(MM,DATEDIFF(MM,0,GETDATE())+1,0))
use above variable to your query
if next time to chage data is very easy...
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply