Help with Error converting data type varcha

• Joe Salvatore

  SSCrazy

  Points: 2174

  More actions

  October 9, 2003 at 9:57 am

  #160818

  I'm going nuts trying to debug this error.

  Thanks in advance to anyone with some insights.

  Query used to test the function:

  SELECT dbo.ConvertDatetimeToJDENumber('09/30/2003 0:00')

  Error message received when running the query to test the function:

  Server: Msg 8114, Level 16, State 5, Procedure ConvertDatetimeToJDENumber, Line 29

  Error converting data type varchar to numeric.

  dbo.ConvertDatetimeToJDENumber function is as follows:

  CREATE FUNCTION dbo.ConvertDatetimeToJDENumber(@InputDatetime DATETIME)

  RETURNS NUMERIC(18,0)

  AS

  BEGIN

  DECLARE @CenturyPartChar AS CHAR (1)

  DECLARE @YearPartChar AS CHAR(2)

  DECLARE @DayOfYearPartChar AS CHAR (3)

  DECLARE @JDETextNumberResult AS CHAR (6)

  DECLARE @JDENumberResult AS NUMERIC(18,0)

  SET @CenturyPartChar =

  LEFT(CAST(DATEPART(yyyy,@InputDatetime) AS CHAR(4)),2)

  SET @YearPartChar =

  CAST(DATEPART(yy,@InputDatetime) AS CHAR(2))

  SET @DayOfYearPartChar =

  CASE WHEN DATEPART(dy,@InputDatetime)< 10 THEN

  CONVERT(CHAR , 0) + CONVERT(CHAR , 0) + CONVERT(CHAR , datepart(dy,@InputDatetime))

  WHEN CAST(DATEPART(dy,@InputDatetime) AS NUMERIC)< 100 THEN

  CONVERT(CHAR , 0) + CONVERT(CHAR , datepart(dy,@InputDatetime))

  ELSE

  CONVERT(CHAR , datepart(dy,@InputDatetime))

  END

  SET @JDETextNumberResult =

  RTRIM(CONVERT(CHAR, (@CenturyPartChar + @YearPartChar + @DayOfYearPartChar)))

  SET @JDENumberResult =

  CONVERT(NUMERIC,@JDETextNumberResult)

  RETURN @JDENumberResult

  END

  

• RD Francis

  SSCarpal Tunnel

  Points: 4867

  More actions

  October 9, 2003 at 10:37 am

  #477740

  Try running your procedure and returning the textnumber. You'll see that it's not translatable (for '1993-10-01, I got 1*274 as the answer).

  Turns out DATEPART(yy,<date>) still returns a four-digit year.

  If I understand your needs, wouldn't this work?

  CREATE FUNCTION dbo.ConvertDatetimeToJDENumber(@InputDatetime DATETIME)

  RETURNS NUMERIC(18,0)

  AS

  BEGIN

  DECLARE @JDENumberResult AS NUMERIC(18,0)

  SET @JDENumberResult = (DATEPART(yyyy,@InputDatetime)*1000) + datepart(dy,@InputDatetime)

  RETURN @JDENumberResult

  END

  R David Francis

  
  R David Francis