Solved

Sql query

Posted on 2016-08-26
12
67 Views
Last Modified: 2016-09-03
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)))'
0
Comment
Question by:RIAS
  • 5
  • 3
  • 2
  • +1
12 Comments
 
LVL 65

Expert Comment

by:Jim Horn
ID: 41771784
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
 

Author Comment

by:RIAS
ID: 41771798
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
 
LVL 65

Expert Comment

by:Jim Horn
ID: 41771802
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
 
LVL 25

Accepted Solution

by:
Lee Savidge earned 250 total points
ID: 41771831
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
 

Author Comment

by:RIAS
ID: 41771834
Cheers mate!
Special Thanks to Jim.
0
[Webinar] Disaster Recovery and Cloud Management

Learn from Unigma and CloudBerry industry veterans which providers are best for certain use cases and how to lower cloud costs, how to grow your Managed Services practice in IaaS clouds, and how to utilize public cloud for Disaster Recovery

 
LVL 46

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 250 total points
ID: 41771839
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
 

Author Comment

by:RIAS
ID: 41771840
Thanks Vitor!!!
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 41771854
Would have helped if you had answered my questions.
0
 

Author Comment

by:RIAS
ID: 41774922
Lee Savidge,

Any suggestion on how to convert to datetime?
0
 
LVL 25

Expert Comment

by:Lee Savidge
ID: 41777882
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
 

Author Comment

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

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how the fundamental information of how to create a table.

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now