Solved

Using forward slash "/" in query

Posted on 2006-10-30
4
1,661 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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Connect to an SQL server ActiveX 10 25
Is this spec enough for a developer or is it just blabble ? 1 36
SQL Quer 4 22
Parse this column 6 27
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.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
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.

860 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