Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2015
    Posts
    27

    Post Unanswered: conversion of variable character error

    I use this statement within a query to get a blank result with a WHEN/THEN statement but keep getting the following error message:

    " The conversion of a varchar data type to a datetime data type resulted in an out-of-range value." Below is the statement:

    CASE

    WHEN (A.APRDAT+'-'+A.APRBAT)= 01/01/1800
    THEN ' '
    ELSE
    Convert(NVARchar(20),A.APRDAT)+'-'+Convert(NVARchar(20),A.APRBAT)

    END AS AUTH_ID,

    Could I get some assistance in getting this to work as design..thanks

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Sure, what's the design? I'm not able to infer either what you have or what you want from the code snippet that you've posted so far.

    Please post the CREATE TABLE statement for your table, some INSERT statements to create 4-5 rows of sample data, the entire SELECT statement that is causing you problems, and the output that you expect based on the other information that you provide.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Is that your actual code?
    If so, your 01/01/1800 is actually resulting in a value of zero, just so you know (it is performing integer math i.e. 1 divided by 1 divided by 1800 = 0).

    What are the data types of APRDAT and APRBAT?
    George
    Home | Blog

  4. #4
    Join Date
    Jan 2015
    Posts
    27
    the data types are INT/date type and below are sample data:
    AUTH_ID
    Jan 1 1800 12:00AM-0
    Jan 1 1800 12:00AM-0
    Jan 1 1800 12:00AM-0
    Jan 1 1800 12:00AM-0
    Jan 1 1800 12:00AM-0
    Jan 1 1800 12:00AM-0
    Jan 1 1800 12:00AM-0

    there are other dates in the table also but we want the '1800' dates to be blank if possible

    Thanks again....

  5. #5
    Join Date
    Jan 2015
    Posts
    27
    I think the issue may be because APRBAT is a time field and APRDAT is a Date field so it's erroring out
    converting the varchar value 'Jan 1 1800 12:00AM' to data type int. The AUTH_ID is a combination of both fields...

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by Khan16 View Post
    the data types are INT/date type and below are sample data:
    I thought that you were using Microsoft SQL Server, but it doesn't have an INT/date type and doesn't format dates the way that you've displayed them. What database engine are you using?

    Using Microsoft SQL Server, you could use something like:
    Code:
    SELECT testDate
    ,  CASE
          WHEN '1900-01-01' <= testDate THEN testDate
       END AS demoDate
       FROM (SELECT DateAdd(month, z.number, '1800-01-01') AS testDate
          FROM master.dbo.spt_values AS z
    	  WHERE  'P' = z.type) AS y
    You would need to use your own column and table names instead of the sub-query based on spt_values that I used for this demo.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  7. #7
    Join Date
    Jan 2015
    Posts
    27
    Thank you....

Tags for this Thread

Posting Permissions

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