[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 93
  • Last Modified:

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)))'
0
RIAS
Asked:
RIAS
  • 5
  • 3
  • 2
  • +1
2 Solutions
 
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
 
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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Lee SavidgeCommented:
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
 
RIASAuthor Commented:
Cheers mate!
Special Thanks to Jim.
0
 
Vitor MontalvãoMSSQL 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

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 5
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now