• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2593
  • Last Modified:

convert date from YYYYMMDD to datetime

2 tables.  1 date conversion.

table1 has a varchar field containing a date formatted like YYYYMMDD ( e.g. 20060531 )

table2 has a datetime type field.

I am needing help writing a trigger that will update a record in table2 immediately after a record is inserted in table1.

Specifically how to convert the date in table1 into something the datetime field in table2 can handle.

The answer needs to work against a MSDE database.

Thank You
0
bbraman
Asked:
bbraman
  • 2
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:

select to_date ( yourfield, 'YYYYMMDD' ) as yourfield from table1
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
sorry, ignore the above, was thinking oracle ...

>I am needing help writing a trigger that will update a record in table2 immediately after a record is inserted in table1.

CREATE TRIGGER trg_copy_row
ON yourtable
FOR INSERT
AS
  INSERT INTO table2 (datetimefield )
   SELECT CONVERT (datetime, yourfield, 112)
  FROM INSERTED
GO


0
 
SireesCommented:
CONVERT(datetime, 'yourfield',101) will convert varchar to datetime.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now