Re: CAST Command

Like this:

CAST(SUBSTRING(CAST(svccod AS VARCHAR), 1, 4) AS DECIMAL(4,0))

--
David Portas
SQL Server MVP
--

"Mike" <noway@xxxxxxxxxxxx> wrote in message
news:qns6f.1047$jV2.300@xxxxxxxxxxxxxxxxxxxxxxxxxxxxx
>I am using MS-Access as a front end for my MS-SQL DB. I have a sql view
>that uses the following:
>
> SELECT TOP 100 PERCENT RECID, PATNUMBER AS [PAT #], SVCCODE AS
> [Service Code], QTY, PROF_CHRGS AS [Pro Fee's'], AMOUNT,
> BILLDATE AS [Bill Date], CHKAMT AS [Check Amt],
> PSDATE AS [Service Date], POSTDATE AS [Post Date], TRNSCODE AS [T Code],
> TRLR AS [T Code Desc], SUBSTRING(CAST(SVCCODE AS
> varchar), 1, 4) AS [Dept #]
> FROM dbo.PAT_Transactions
> ORDER BY PATNUMBER, SVCCODE
>
> My problem is the cast command. Will this sql view works and cast the
> SVCCODE field into a varchar, I need to cast the reseult of this,
> SUBSTRING(CAST(SVCCODE AS varchar), 1, 4) AS [Dept #], back in to a
> decimal format. I am dropping this view onto a form and need to link to a
> field on the form that is in decimal format.
>
> The only way I could get this to work was to create another view, based on
> the one above, and cast the [Dept #] field back into the decimal format.
> Is there any better way to do this? Can I nest the Cast command?
>
> Hope this makes sense.
>
> Thanks in advance.
>
> Mike
> m charney at dunlap hospital dot org
>
>
>
>


.

Relevant Pages

  • Re: CAST Command
    ... > My problem is the cast command. ... Will this sql view works and cast the ... > SVCCODE field into a varchar, I need to cast the reseult of this, ...
    (comp.databases.ms-sqlserver)
  • Re: CAST conversion error: Syntax Error missing operator
    ... AFAIK, neither Access, VBA or Jet SQL will recognize CAST, ... I have to do a series of updates to said data ... "import" table I work with to manipulate the data before inserting. ...
    (microsoft.public.access.formscoding)
  • Re: Date time stamp shows different
    ... I think you will need to cast it to a double and do date/time calculations ... of a second so the milliseconds get stripped out out of the conversion, ... When I run an SQL ... > //Get the reverse date time stamp so that we can use it for later. ...
    (microsoft.public.vc.database)
  • Re: Invalid CastException with SqlDBType
    ... Add(string*, SqlDbType, int, string**) ... The DateTime.Now is trying to cast into a int. ... not in Sql set up the column as identity and let it seed itself. ... > update a MS SQL Database with a stored procedure. ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: Text field that is a date
    ... passing the SQL thru to the datasource and specifying the return type there. ... You're foxpro app will have to match the datatype returned by the DBMS. ... As long as your code only runs against the SQL server ... database it should be ok to use the CAST or Convert. ...
    (microsoft.public.vb.database.ado)
Loading