Solved

Using forward slash "/" in query

Posted on 2006-10-30
4
1,662 Views
Last Modified: 2012-08-13
I am trying to update a table using the following query:
update [daily sales totals by account type]
set [date] = mo+'/'+dy+'/'+YR

MO, DY and YR are fields in the table [daily sales totals by account type] and are all int.  [date] is a varchar field.

I am getting the following error:
Server: Msg 245, Level 16, State 1, Line 1
Syntax error converting the varchar value '/' to a column of data type int.

I have tried using CAST, CONVERT, & (instead of +), single quotes, double quotes and have not been able to get this one to work.  I am needing a swift kick in the butt - 'cuz I'm sure this one is something simple that I am overlooking...been working on this issue too long...

Can someone please tell me where I am going wrong?

Thanks!

Sean
0
Comment
Question by:saladart
4 Comments
 
LVL 29

Accepted Solution

by:
Nightman earned 500 total points
ID: 17838118
update [daily sales totals by account type]
set [date] =cast(mo as varchar(2)) + '/' + cast(dy as varchar(2))  + '/' + cast(YR as varchar(2))
0
 
LVL 11

Expert Comment

by:rw3admin
ID: 17838122
cause your mo, dy and YR are int
try
set [date] = cast(mo as varchar)+'/'+cast(dy as varchar)+'/'+cast(YR as varchar)
0
 

Author Comment

by:saladart
ID: 17838209
See - I knew it was something simple...

Thanks Nightman - you were first to answer...

Sean
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 17838214
SQL will automatically recognize YYYYMMDD as a valid format, so this is easier to do:

UPDATE [daily sales totals by account type]
SET [date] = yr * 10000 + mo * 100 + dy
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
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 INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

680 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