Convert Varchar to Numeric while varchar field has comma's

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Convert Varchar to Numeric while varchar field has comma's

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-05-20 : 07:26:27

Daniel writes "MS Server 2003, IIS 6
MS SQL2000

Some background. This is an events management database. It books rooms and allows user to sign up for events. All events must have a room. Everything is related through the Events Table by EventID.

I am trying to get rooms in use on a specific date and specific Campus (and some other user collected data, maximum capicity).

Tables:
Rooms
-RoomID, numeric

RoomInfo
-RoomID, (can hold multiple rooms i.e: 1, 2), Varchar

EventRoom (how rooms get booked for use)
-RoomID, numeric
-EventID, numeric

Events
-EventID, numeric

Variables:
stype
maxcap
rDate



SELECT RoomInfo.RoomID, RoomInfo.Number, RoomInfo.Name, Events.EDate, Events.StartTime, Events.EndTime, Events.CampusID

FROM RoomInfo INNER JOIN EventRoom ON Convert(Numeric(9, 16)RoomInfo.RoomID) = EventRoom.RoomID INNER JOIN Events ON EventRoom.EventID = Events.EventID

WHERE RoomInfo.CampusID = "&CampusID&" AND RoomInfo."&stype&" >= "&maxcap&" AND RoomInfo."&stype&" <= "&maxcap+10&" AND

There is more to the statement based on which options they choose.

[morecode]
if datediff("d", date, rDate) >= 358 Then
strSQL = strSQL & " Events.EDate > '"& DateAdd("d", -2, rDate) & "' AND Events.EDate < '" & DateAdd("d", 7, rDate) &"' AND Events.Status = 'Approved' AND Events.CampusID = "&CampusID
Else
dim md
md = (datediff("d", date, rDate) - 365)
strSQL = strSQL & " Events.EDate > '"& DateAdd("d", -2, rDate) & "' AND Events.EDate < '" & DateAdd("d", md, rDate) &"' AND Events.Status = 'Approved' AND Events.CampusID = "&CampusID
End if

'----------------------------------------------------------
If Request.Form("selEquip") <> "" Then
If Request.Form("selEquip") = "Plasma" Then
strSQL = strSQL & " AND RoomInfo.Plasma = 'Yes'"
sql2 = sql2 & " AND RoomInfo.Plasma = 'Yes'"
End If
If Request.Form("selEquip") = "Overhead" Then
strSQL = strSQL & " AND RoomInfo.OHP = 'Yes'"
sql2 = sql2 & " AND RoomInfo.OHP = 'Yes'"
End If
If Request.Form("selEquip") = "PA" Then
strSQL = strSQL & " AND RoomInfo.PA = 'Yes'"
sql2 = sql2 & " AND RoomInfo.PA = 'Yes'"
End If
If Request.Form("selEquip") = "Computer" Then
strSQL = strSQL & " AND RoomInfo.Computer = 'Yes'"
sql2 = sql2 & " AND RoomInfo.Computer = 'Yes'"
End If
End If

'----------------------------------------------------------
If Request.Form("radSEI") <> "" Then
If Request.Form("radSEI") = "VC" Then
strSQL = strSQL & " And RoomInfo.VideoConf = 'Yes'"
sql2 = sql2 & " And RoomInfo.VideoConf = 'Yes'"
End If
If Request.Form("radSEI") = "OVC" Then
strSQL = strSQL & " And RoomInfo.VideoConf = 'Yes'"
sql2 = sql2 & " And RoomInfo.VideoConf = 'Yes'"
End If
End If
[/morecode]"

nr
SQLTeam MVY

12543 Posts

Posted - 2005-05-20 : 07:36:45

Convert(Numeric(9, 16), RoomInfo.RoomID)

was missing a comma.
Is that the problem? You don't say what the question is.

maybe
Convert(Numeric(9, 16), replace(RoomInfo.RoomID, ',',''))
or
Convert(Numeric(9, 16), replace(replace(RoomInfo.RoomID, '.',''),',','.'))

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources