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)))'
RIASAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Lee SavidgeConnect With a Mentor Commented:
They both depend on the initial data types of the variables. I will assume @Date is a datetime. If so, and you want the whole datetime string then you'll need a bigger varchar than 20 as it will be in the form:

yyyy-mm-dd hh:mi:ss.mmm

which is 23 chars, so 25 is better. If you only really want to capture the date then you can truncate the time. I'll assume you want the full datetime.

CAST('''+ convert(nvarchar(25), @date, 121) + '''  AS  varchar(25)),
CAST('''+ cast(@Year_Ref as nvarchar(4)) +'''  AS INTEGER),

Open in new window

0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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.
0
 
RIASAuthor Commented:
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
0
A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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.
1
 
RIASAuthor Commented:
Cheers mate!
Special Thanks to Jim.
0
 
Vitor MontalvãoConnect With a Mentor MSSQL Senior EngineerCommented:
You need to provide the data type of the variables.
You can always try the following to see if it works (I'm guessing they're all varchar):

INSERT INTO Table1(SQL) VALUES ( 'INSERT INTO table2
(
                   [Ref],
                   [Auth],
                   [Date],
                   [Year_Ref],                  
                   [FaxAuth],            
                   [Attachments],
                   [FaxNo],
                   [Attn],                  
                 [FaxLetterMemo],
                   [To],
                 [Regarding],
                   [SentBy]
             ) 
             VALUES
             ('
                +   ''' + @Ref + ''',' 
                +  ''' + @Auth+ ''',' 
( do the same for the others variables...)
                 + ''' + @SentBy + ''')'

Open in new window

1
 
RIASAuthor Commented:
Thanks Vitor!!!
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Would have helped if you had answered my questions.
0
 
RIASAuthor Commented:
Lee Savidge,

Any suggestion on how to convert to datetime?
0
 
Lee SavidgeCommented:
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.
0
 
RIASAuthor Commented:
CAST('''+ @Date +'''  AS  varchar(20)),
Date field is actually Datetime and also it fails if not value is passed to it
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.