Updating Date - 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

Updating Date

Author

Topic

Sambhav
Starting Member

31 Posts

Posted - 2007-07-02 : 12:17:50

Hi,
I have around 8 date columns in a table I want to update the columns
for eg initialization date should be greater than incident date .but the thing is it should be just one day or upto 4 days greater .

Right now all the tables have random dates ..I tried dateadd i also tried datediff nonis helping please can u help

sambhav jain

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-02 : 12:23:46

Please provide some sample data and expected output. Also provide some DDL (table layout).

Peter Larsson
Helsingborg, Sweden

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2007-07-02 : 12:26:04

dang it Pete, quit answering before i get done reading them!

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Sambhav
Starting Member

31 Posts

Posted - 2007-07-02 : 12:35:07

I tried doing this

SELECT incidentdt,
initdt,
DATEDIFF(MONTH, incidentdt, initdt) AS [Month DatePart>1],
DATEDIFF(YEAR, incidentdt, initdt)AS [Year DatePart>1]
FROM Incidents

initdt month years incidentdt
2006-09-14 18:48:41.000 58 5 1997-09-05 18:02:38.000
2005-10-26 21:18:34.000 97 8 2005-12-15 06:07:17.000
2006-01-07 21:23:21.000 1 1 2004-07-29 20:32:14.000
2004-08-04 16:29:32.000 1 0 2002-05-25 15:22:36.000
2003-01-18 03:42:45.000 8 1 1996-02-16 13:49:49.000
2006-07-25 03:55:10.000 125 10 2005-02-17 05:47:47.000
2005-09-05 03:21:05.000 7 0
I got this output so here as you can see that the difference between incidentdt and initdt is in years i just want the difference to be a day or atmost 4 days

sambhav jain

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-02 : 12:39:22

where datediff(hours, incidentdt, initdt) <= 96

Peter Larsson
Helsingborg, Sweden

Ifor
Aged Yak Warrior

700 Posts

Posted - 2007-07-02 : 12:45:58

Maybe:

UPDATE Incidents
SET incidentdt = initdt + DATEPART(hour, initdt)%4 + 1

Sambhav
Starting Member

31 Posts

Posted - 2007-07-02 : 12:50:24

It is giving me this Error
Server: Msg 155, Level 15, State 1, Line 6
'hours' is not a recognized datediff option.

and I have to update the existing date not just select

...I am sorry for bothering you
that is where ever the date difference is more than 4 days I want it to become within 4 days ...

sambhav jain

Sambhav
Starting Member

31 Posts

Posted - 2007-07-02 : 13:01:37

Thanks a lot

!!!
It worked you r amazing thanks once again

sambhav jain

Sambhav
Starting Member

31 Posts

Posted - 2007-07-02 : 15:54:08

Hi
I have to update the incidents table.
The initiator and initdept are two corresponding columns whoes value I have to get from another table Employee where (initiator means Empname and initdept means title)
if i just do

select Empname,title from employee
it gives me correct output while
select initiator,initdept from incidents

It gives the initiator correct but instead of giving the initdept
it gives output as initaiator the out put is given below

initiator initdept
Jeff C. Taylor Jeff C. Taylor
Randy S. Jonas Randy S. Jonas
Mike lewis Mike lewis

it should give the out put like

initiator initdept
Jeff C. Taylor Software Engg
Randy S. Jonas Tester

I hope you got my question!
now I hav to update incidents table such that it should give initiator as well as its corrosponding initdept

thanks

sambhav jain

Sambhav
Starting Member

31 Posts

Posted - 2007-07-03 : 10:36:42

No body is helping me

!!!!!!!!!!!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-03 : 11:29:45

Maybe if you stop adding new questions to old problems, someone will.
If you have a new problem, start a new thread.

Peter Larsson
Helsingborg, Sweden

Sambhav
Starting Member

31 Posts

Posted - 2007-07-03 : 11:40:02

Ohhk thanks ..i will right away do that !!

sambhav jain

Sambhav
Starting Member

31 Posts

Posted - 2007-07-06 : 16:14:35

I am stuck now with new problem of updatig date this time I have to select date for MFGDT which is in PRODWIN Table that date should be 2 or 3 months before AWAREDT which is in INCIDENT Table

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-07-06 : 16:29:51

If it's a new problem, start a new thread as was suggested already by Peter. Perhaps saying it twice will make it clear.

Tara Kizer
http://weblogs.sqlteam.com/tarad/

Sambhav
Starting Member

31 Posts

Posted - 2007-07-06 : 16:34:22

ok

sambhav jain

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources