Solved

Sql query

Posted on 2016-08-26
12
65 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
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 
LVL 45

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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

744 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

9 Experts available now in Live!

Get 1:1 Help Now