Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067

    Unanswered: Convertion VarChar Error

    Ive got a small problem at the moment

    I have ran a query that has been used for a while now and have recieved this error

    Code:
    Server: Msg 245, Level 16, State 1, Line 3
    Syntax error converting the varchar value 'N' to a column of data type int.
    Ive searched the data and the only value of N that i can find is currently sitting in a field where the field type is varchar

    is there a workaround for this, Ive tried running a case statement to set the N to 0 and also tried casting

    Cheers in advance
    Dave
    Last edited by DavidCoutts; 01-12-05 at 08:09. Reason: sorting tags

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by DavidCoutts
    ...the only value of N that i can find is currently sitting in a field where the field type is varchar
    so why is it trying to convert this value to an integer?

    i have no idea, because i can't see your query from here

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    rudy man i thought you were psychic

    Code:
    -- 5302 MH consultant OP first attendances
    
    SELECT	CdsType,
    	NHSTrust,
    	AttendedOrDNACode, --N apears here varchar(1) column
    	FirstAttendanceCode,
    	SpecialtyCode,
     	Specialty,
    	PCG,
    	PurchCode,
    	datepart (year, ActivityDate) as yearAct,
    	datepart (month, ActivityDate) as monthAct
    
    FROM   dbo.VIEW_Outpatient2000_Analysis
    
    WHERE	PurchCode like '5KW%'
    --pcg like 'Chelt%'
    	and  NHSTrust not like 'Glou%'
    	and FirstAttendanceCode = 1
    	and AttendedOrDNACode in ('5', '6', '1','N')
    	and SpecialtyCode between '710' and '715'
    	and ((datepart (year, ActivityDate) = 2004
     	and datepart (month, ActivityDate) > 03)
    	or (datepart (year, ActivityDate) = 2005
     	and datepart (month, ActivityDate) < 04))
    So im pretty much confused, cant see anything there that would cause a problem

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    the 'N' may not necessarily be where you think it is

    FirstAttendanceCode = 1
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    Ah Blind as a Bat i am
    Cheers rudy

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •