CODEWITHSUNDEEP
sqlsql-server
Red Devil
Red Devil

Reputation: 2393

Need to change the startdate as per the month

I have a table something like this. workershiftid column is auto-increment.

I have to create a job which runs on first of every month and change the start and end date accordingly rest all will be same.

job:

Workershiftid   |   entityid    |   startdate   |   enddate     |   shiftid 
1               |   1           |   2016-06-01  |   2016-06-30  |   1   
2               |   2           |   2016-06-01  |   2016-06-30  |   4   
3               |   4           |   2016-06-01  |   2016-06-30  |   6  
4               |   7           |   2016-06-01  |   2016-06-30  |   14

I tried this query:

insert into job (entityid, startdate, enddate, shiftid)
select entityid, startdate, enddate, shiftid 
from job 
where startdate = '2016-06-01'

But it is giving output as

Workershiftid   |   entityid    |   startdate   |   enddate     |   shiftid  
1               |   1           |   2016-06-01  |   2016-06-30  |   1   
2               |   2           |   2016-06-01  |   2016-06-30  |   4  
3               |   4           |   2016-06-01  |   2016-06-30  |   6  
4               |   7           |   2016-06-01  |   2016-06-30  |   14  
5               |   1           |   2016-06-01  |   2016-06-30  |   1   
6               |   2           |   2016-06-01  |   2016-06-30  |   4   
7               |   4           |   2016-06-01  |   2016-06-30  |   6  
8               |   7           |   2016-06-01  |   2016-06-30  |   14

How can I change the startdate to 2016-07-01 if I am running it on 1st July?

Upvotes: 0

Views: 64

Answers (2)

lvoros
lvoros

Reputation: 312

First and last days of a month can be calculated like:

SELECT DATEADD(month, DATEDIFF(month, 0, getdate()), 0) AS StartOfMonth
SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0)) as EndOfMonth

So you can try something like this:

declare @start datetime
declare @end datetime

SELECT @start = DATEADD(month, DATEDIFF(month, 0, getdate()), 0)
SELECT @end = DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0))

insert into job (entityid,startdate,startdate,shiftid)
select entityid,@start,@end,shiftid 
from job 
where startdate='2016-06-01'

Upvotes: 1

Pரதீப்
Pரதீப்

Reputation: 93704

Use GETDATE() to know the current date

select entityid,startdate,enddate,shiftid 
from job where startdate=cast(getdate() as date)

Getdate() will result date with time. To remove time part from getdate() use CAST

In case you want to get the first day of current month then use this 

Select CAST(DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0) AS DATE)

Upvotes: 0

Related Questions