Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2003
    Location
    Australia
    Posts
    46

    Question Unanswered: Defining Variables in Date fields within a Trigger??

    Hi All,

    I am creating an Insert Trigger with following example of code for you to go off(just an example)


    DECLARE @CREATIONDATE VARCHAR(12)
    SET @CREATIONDATE = (Select Inserted.Creation_Date from Inserted)

    Insert into fintest.dbo.glf_chart_acct(fintest.dbo.chart_name, fintest.dbo.accnbri, fintest.dbo.descr1, fintest.dbo.date)
    Values ('Name', 'Code', 'Description', {d @CREATIONDATE})

    Inserted.CreationDate is Varchar and the fintest.dbo.date colunm is a datetime field

    When checking the Syntax for the trigger it errors saying that - 'Error Syntax near '@CREATIONDATE'

    It works fine if I just insert a static value such as
    {d '2002-10-10'}. Am I able to replace the static value with a variable and if so what will my syntax be? How would it look?

    Thanks
    Anthony

  2. #2
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    how about:

    Code:
    Insert into fintest.dbo.glf_chart_acct
           (chart_name, accnbri, descr1, date) 
    select 'Name', 'Code', 'Description', Creation_Date 
      from Inserted
    SQL Server will automatically convert a string to a date and you have the advantage of handeling one or more records at a time!
    Paul Young
    (Knowledge is power! Get some!)

  3. #3
    Join Date
    Jan 2003
    Posts
    6

    Re: Defining Variables in Date fields within a Trigger??

    DECLARE @CREATIONDATE VARCHAR(12)
    SET @CREATIONDATE = (Select Inserted.Creation_Date from Inserted)

    Insert into fintest.dbo.glf_chart_acct(fintest.dbo.chart_name, fintest.dbo.accnbri, fintest.dbo.descr1, fintest.dbo.date)
    Values ('Name', 'Code', 'Description', {d @CREATIONDATE})

    Inserted.CreationDate is Varchar and the fintest.dbo.date colunm is a datetime field

    How about instead of the {d @CREATIONDATE} you either put just @CREATIONDATE or try a CAST(@CREATIONDATE as datetime)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •