General discussion

  • Creator
    Topic
  • #2276529

    Date Format and correlated subquery

    Locked

    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

All Comments

  • Author
    Replies
    • #3313274

      This has been tested.

      by gizmo59 ·

      In reply to Date Format and correlated subquery

      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

    • #2602834

      Why selfjoin?

      by msi77 ·

      In reply to Date Format and correlated subquery

      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) != ‘ ‘

Viewing 1 reply thread