Update a datetime field - SQL Server Forums

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

Update a datetime field

Author

Topic

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2013-05-23 : 06:17:11

Hi,

I have two fields - period_startdate and period_enddate and on my table, sample shown below

Prac period_startdate period_startdate
1 01/04/13 00:00:00 30/06/2013 00:00:00
2 01/04/13 00:00:00 30/06/2013 00:00:00
3 01/04/13 00:00:00 30/06/2013 00:00:00
4 01/04/13 00:00:00 30/06/2013 00:00:00

I want to write an SQL stetement that will change the dates as shown below;
Prac period_startdate period_startdate
1 01/01/13 00:00:00 31/03/2013 00:00:00
2 01/01/13 00:00:00 31/03/2013 00:00:00
3 01/01/13 00:00:00 31/03/2013 00:00:00
4 01/01/13 00:00:00 31/03/2013 00:00:00

Thank you

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-23 : 06:28:30

[code]
SELECT Prac,
DATEADD(mm,DATEDIFF(mm,0,period_startdate)-3,0) AS period_startdate,
DATEADD(mm,DATEDIFF(mm,0,period_enddate)-2,0)-1 AS period_enddate
FROM table
[/code]

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-23 : 06:29:48

In case you need to update values in main table make it like below


UPDATE table
SET period_startdate = DATEADD(mm,DATEDIFF(mm,0,period_startdate)-3,0),
period_enddate = DATEADD(mm,DATEDIFF(mm,0,period_enddate)-2,0)-1

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

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2013-05-23 : 08:18:11

This will update all the period_startdate and period_enddate, in this scenario I want to be specific ONLY

1) period_startdate = 01/04/13 00:00:00 should be replaced with 01/01/13 00:00:00
2) period_enddate = 30/06/13 00:00:00 should be replaced with 31/03/13 00:00:00

All other period_startdates and period end_dates SHOULD NOT be affected..

Thank you

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-23 : 09:59:50

[code]

UPDATE table
SET period_startdate = DATEADD(mm,DATEDIFF(mm,0,period_startdate)-3,0)
WHERE period_startdate = '20130401'

UPDATE table
SET period_enddate = DATEADD(mm,DATEDIFF(mm,0,period_enddate)-2,0)-1
WHERE period_enddate = '20130630'
[/code]

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources