Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Sql query

Posted on 2016-08-26
12
Medium Priority
?
84 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 2
  • +1
12 Comments
 
LVL 66

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 66

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
Quick Cloud Training

Looking for some quick training on the cloud in 2 hours or less? Check out these how-to guides in AWS, Linux, OpenStack, Azure, and more!

 
LVL 25

Accepted Solution

by:
Lee Savidge earned 1000 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
 
LVL 52

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 1000 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 66

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

Setting up LaraDock for Laravel

Learn how to set up LaraDock in a Laravel project - LaraDock gives us an easy way to run a Laravel application using Docker in a single command.

Question has a verified solution.

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

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
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 to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

715 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