General discussion

Locked

Date Format and correlated subquery

By Randy.L.Kemp ·
I need to take a month and day field in a table, and update a new date field, based upon the month and year. I tried this query, which says it updates the records, but none of the date fields are updated. Can someone tell me what is wrong with the query or suggest a better one?
UPDATE dbo.pd_products SET launch_date =
(select cast( B.launch_year + '-' + B.launch_month + '-' + '1' AS datetime)
from dbo.pd_products A join dbo.pd_products B
on A.product_key = B.product_key
and A.launch_date = B.launch_date)
where substring(launch_year,1,1) != ' '
and substring(launch_month,1,1) != ' '
I tried the query in SQL Server Query Analyzer. While it says it updated the rows (all 500), all the launch_date fields were null. Is this the query or how the data fields are defined? Here are the particulars:
Column_name data_type type_name Column_size
launch_month 12 varchar 2
launch_year 12 varchar 4
launch_date 93 datetime 23

This conversation is currently closed to new comments.

2 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

This has been tested.

by gizmo59 In reply to Date Format and correlate ...

Use this code, obviously changing table and columns.

DECLARE @PDK int
Declare @IDT as datetime

DECLARE insert_cursor CURSOR FOR
select cast( B.launch_year + '-' + B.launch_month + '-' + '1' AS datetime),a.PDT_KEY
from dbo.pd_products A join dbo.pd_products B
on A.PDT_KEY = B.PDT_KEY


OPEN insert_cursor

FETCH NEXT FROM insert_cursor
INTO @IDT, @PDK

WHILE @@FETCH_STATUS = 0
BEGIN


Update dbo.pd_products
set dbo.pd_products.launch_date= @IDT
Where dbo.pd_products.PDT_KEY = @PDK


-- This is executed as long as the previous fetch succeeds.
FETCH NEXT FROM insert_cursor
INTO @IDT,@PDK
END


CLOSE insert_cursor
DEALLOCATE insert_cursor


Good luck

Collapse -

Why selfjoin?

by msi77 In reply to Date Format and correlate ...

I don't understand why do you use selfjoin of tables.
Try
UPDATE dbo.pd_products SET launch_date =
(select cast( launch_year + '-' + launch_month + '-' + '01' AS datetime)
where substring(launch_year,1,1) != ' '
and substring(launch_month,1,1) != ' '

Back to Web Development Forum
2 total posts (Page 1 of 1)  

Related Discussions

Related Forums