[ResolvUpdate field with values from another field

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

SQL Server 2005 Forums

Transact-SQL (2005)

[ResolvUpdate field with values from another field

Author

Topic

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2011-08-26 : 17:01:28

In my stored procedure i have a temp table that amongs others contains a datetime field and varchar(10)n field.

In the datetime field I have the date (but not the time), in the varchar field I have the time.

Example from temp table

DateTime field ...................... VarChar field

2011-07-12 00:00:00.000	   17:38:40
2011-07-13 00:00:00.000    10:18:28
2011-07-08 00:00:00.000	   10:21:32
2011-07-08 00:00:00.000	   13:36:56
2011-08-03 00:00:00.000	   15:46:41
2011-07-12 00:00:00.000	   14:10:13

How can I update the datetime field with values from varchar?

Thank you.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-08-26 : 17:11:24

You can use DATEADD function since your datetime field has 00:00 as the time.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-27 : 06:25:47

see logic below


--Sample table to illustrate logic
CREATE TABLE DateMerge
(Dt DateTime,
Tm VarChar(10)
)

--fill it with your sample data
INSERT INTO DateMerge

SELECT Dt,Tm
FROM
(
SELECT '2011-07-12 00:00:00.000' AS Dt,	   '17:38:40' AS Tm UNION ALL
SELECT '2011-07-13 00:00:00.000','10:18:28' UNION ALL
SELECT '2011-07-08 00:00:00.000','10:21:32'UNION ALL
SELECT '2011-07-08 00:00:00.000',' 13:36:56'UNION ALL
SELECT '2011-08-03 00:00:00.000','15:46:41'UNION ALL
SELECT '2011-07-12 00:00:00.000','14:10:13'
)t

--display the sample data
SELECT 'BEFORE UPDATE'
SELECT * FROM DateMerge

--Apply update logic (THIS IS YOUR SOLUTION!!!)
UPDATE YourTable
SET Datefield=DATEADD(ss,DATEDIFF(ss,0,TimeField),Datefield)


--Check the updated values
SELECT 'After Update'
SELECT * FROM DateMerge 

output
------------------------------------------------------
BEFORE UPDATE

Dt                      Tm
----------------------- ----------
2011-07-12 00:00:00.000 17:38:40
2011-07-13 00:00:00.000 10:18:28
2011-07-08 00:00:00.000 10:21:32
2011-07-08 00:00:00.000  13:36:56
2011-08-03 00:00:00.000 15:46:41
2011-07-12 00:00:00.000 14:10:13


------------
After Update

Dt                      Tm
----------------------- ----------
2011-07-12 17:38:40.000 17:38:40
2011-07-13 10:18:28.000 10:18:28
2011-07-08 10:21:32.000 10:21:32
2011-07-08 13:36:56.000  13:36:56
2011-08-03 15:46:41.000 15:46:41
2011-07-12 14:10:13.000 14:10:13

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2011-08-29 : 17:03:19

Worked. I modified a little and have waht I needed.

I used a temp table so here it is:

UPDATE #Table1
SET shumpj_datetime=DATEADD(ss,DATEDIFF(ss,0,shtday_time),shumpj_datetime)

AGAIN, thank you.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources