update with dissimilar date manipulation

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)

update with dissimilar date manipulation

Author

Topic

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2008-09-24 : 07:07:00

I have two tables in SQL Server 2005

table a
-------
name varchar(50)
address (varchar(50)
date (datetime)
amount (smallmoney)

table b
-------
name varchar(50)
address varchar(50)
date (varchar(8))
amount (smallmoney)

I need to update table a, setting table a.amount to table b.amount where
table a.name = table b.name
table a.address = table b.address
table a.date = table b.date

However, table b's date format is weird in that I need only to compare the last 4 characters of the table b date (string) with the month and day portion of table a's date (datetime) field.

So,

table a
-------

bill
the street
2008-02-04 00:00:00
null

table b
-------
bill
the street
1080204
106.25

will give me

table a
-------
bill
the street
2008-02-04 00:00:00
106.25

Hope that makes sense :)

Not sure how to this. Any pointers would be very much appreciated.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-24 : 07:17:15

[code]update a
set a.amount=b.amount
from [table a] a
inner join [table b] b
on a.name=b.name
and a.address=b.address
and month(a.date)=left(right(b.date,4),2)
and day(a.date)=right(b.date,2)[/code]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-24 : 07:21:10

[code]DECLARE @tableA TABLE
(
name VARCHAR(50),
address VARCHAR(50),
date DATETIME,
amount SMALLMONEY
)

INSERT @tableA
SELECT 'bill', 'the street', '2008-02-04 00:00:00', null

DECLARE @tableB TABLE
(
name VARCHAR(50),
address VARCHAR(50),
date VARCHAR(8),
amount SMALLMONEY
)

INSERT @tableB
SELECT 'bill', 'the street', '1080204', 106.25

SELECT *
FROM @tableA

UPDATE a
SET a.amount = b.amount
FROM @tableA AS a
INNER JOIN @tableB AS b ON b.name = a.name
AND b.address = a.address
AND RIGHT(b.date, 4) = RIGHT(CONVERT(CHAR(6), a.date, 12), 4)

SELECT *
FROM @tableA[/code]

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources