asked on

Sql query

Hello,
I have this query which fails to convert date and integer field

-- INSERT INTO Debug(SQL) VALUES ( 'INSERT INTO Table1
      --(
      --      [Ref],
      --      [Auth],
     --      [Date],
      --      [Year_Ref],
      --      [FaxAuth],
      --      [Attachments],
      --      [FaxNo],
      --      [Attn],
      -- [FaxLetterMemo],
      --      [To],
      -- [Regarding],
      --      [SentBy]
      --)
      --VALUES
      --(
      --      CAST(''' +@Ref + ''' AS Nvarchar(50)),
      --      CAST('''+@Auth+''' AS NVARCHAR(255)),
      -- CAST('''+ @Date +''' AS varchar(20)),
      --      CAST('''+@Year_Ref+''' AS INTEGER),
      --      CAST('''+@FaxAuth +''' AS Nvarchar(255)),
      --      CAST('''+ @Attachments+''' AS Nvarchar(255)),
      --      CAST('''+@FaxNo+''' AS Nvarchar(255)),
      --      CAST('''+@Attn+''' AS Nvarchar(255)),
      --      CAST('''+@FaxLetterMemo +''' AS Nvarchar(255)),
      --      CAST('''+@To +''' AS Nvarchar(255)),
      --      CAST('''+@Regarding +''' AS Nvarchar(255)),
      --      CAST('''+@SentBy+''' AS Nvarchar(255)))'

Jim Horn

For starters..

All of the above code is commented out with --, which means it is not executed. Why's that?

If you are feeding all of this into a varchar variable, hence the '''s, please spell that out and paste the entire code bloc. If yes, also please tell us why that is necessary, as executing the statement without that would save a lot of development time.

Would help if you told us what @date and @Year_Ref are by copy-pasting that code. Perhaps they are not convertable to a date and int.

RIAS

ASKER

Hello Jim ,

       INSERT INTO Table1(SQL) VALUES ( 'INSERT INTO table2
            (
                  [Ref],
                  [Auth],
                  [Date],
                  [Year_Ref],
                  [FaxAuth],
                  [Attachments],
                  [FaxNo],
                  [Attn],
              [FaxLetterMemo],
                  [To],
              [Regarding],
                  [SentBy]
            )
            VALUES
            (
                  CAST(''' +@Ref + ''' AS Nvarchar(50)),
                  CAST('''+@Auth+''' AS NVARCHAR(255)),
              CAST('''+ @Date +''' AS varchar(20)),
                  CAST('''+@Year_Ref+''' AS INTEGER),
                  CAST('''+@FaxAuth +''' AS Nvarchar(255)),
                  CAST('''+ @Attachments+''' AS Nvarchar(255)),
                  CAST('''+@FaxNo+''' AS Nvarchar(255)),
                  CAST('''+@Attn+''' AS Nvarchar(255)),
                  CAST('''+@FaxLetterMemo +''' AS Nvarchar(255)),
                  CAST('''+@To +''' AS Nvarchar(255)),
                  CAST('''+@Regarding +''' AS Nvarchar(255)),
                  CAST('''+@SentBy+''' AS Nvarchar(255)))'

its giving the error on the bold

Cheers

Jim Horn

Ok. Let me know if you have answers to my questions.

Also what you have in bold is not an error message, it's apparently the code that is throwing the error. Please copy-paste the exact error message into this question.

ASKER CERTIFIED SOLUTION

Lee

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

RIAS

ASKER

Cheers mate!
Special Thanks to Jim.

SOLUTION

Vitor Montalvão

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

RIAS

ASKER

Thanks Vitor!!!

Jim Horn

Would have helped if you had answered my questions.

RIAS

ASKER

Lee Savidge,

Any suggestion on how to convert to datetime?

Lee

What seems to be the problem?

If you want to store the date/time as a datetime then your target table needs to have the same data types for the target column.

RIAS

ASKER

CAST('''+ @Date +''' AS varchar(20)),
Date field is actually Datetime and also it fails if not value is passed to it