Why Would Year Fail with a Conversion Error from a Date

Why would YEAR fail with a conversion error from a Date?

I assume that RValues is a string column of some type, for some reason. You should fix that and store date data using a date data type (obviously in a separate column than this mixed bag).

If you can't fix that, then you can prevent what Damien described above by:

CASE WHEN ISDATE(RValues) = 1 THEN CONVERT(Date, RValues) END AS FechaFirma 

(Which will make the "date" NULL if SQL Server can't figure out how to convert it to a date.)

You can't prevent this simply by adding a WHERE clause, because SQL Server will often try to attempt the conversion in the SELECT list before performing the filter (all depends on the plan). You also can't force the order of operations by using a subquery, CTE, join order hints, etc. There is an open Connect item about this issue - they are "aware of it" and "hope to address it in a future version."

Short of a CASE expression, which forces SQL Server to evaluate the ISDATE() result before attempting to convert (as long as no aggregates are present in any of the branches), you could:

• dump the filtered results into a #temp table, and then subsequently select from that #temp table, and only apply the convert then.
• just return the string, and treat it as a date on the client, and pull YEAR/MONTH etc. parts out of it there
• just use string manipulation to pull YEAR = LEFT(col,4) etc.

• use TRY_CONVERT() since I just noticed you're on SQL Server 2012:

  TRY_CONVERT(DATE, RValues) AS FechaFirma
  

Conversion failed when converting date and/or time from character string while inserting datetime

There are many formats supported by SQL Server - see the MSDN Books Online on CAST and CONVERT. Most of those formats are dependent on what settings you have - therefore, these settings might work some times - and sometimes not.

The way to solve this is to use the (slightly adapted) ISO-8601 date format that is supported by SQL Server - this format works always - regardless of your SQL Server language and dateformat settings.

The ISO-8601 format is supported by SQL Server comes in two flavors:

• YYYYMMDD for just dates (no time portion); note here: no dashes!, that's very important! YYYY-MM-DD is NOT independent of the dateformat settings in your SQL Server and will NOT work in all situations!

or:

• YYYY-MM-DDTHH:MM:SS for dates and times - note here: this format has dashes (but they can be omitted), and a fixed T as delimiter between the date and time portion of your DATETIME.

This is valid for SQL Server 2000 and newer.

So in your specific case - use these strings:

insert into table1 values('2012-02-21T18:10:00', '2012-01-01T00:00:00');

and you should be fine (note: you need to use the international 24-hour format rather than 12-hour AM/PM format for this).

Alternatively: if you're on SQL Server 2008 or newer, you could also use the DATETIME2 datatype (instead of plain DATETIME) and your current INSERT would just work without any problems! :-) DATETIME2 is a lot better and a lot less picky on conversions - and it's the recommend date/time data types for SQL Server 2008 or newer anyway.

SELECT
   CAST('02-21-2012 6:10:00 PM' AS DATETIME2),     -- works just fine
   CAST('01-01-2012 12:00:00 AM' AS DATETIME2)   -- works just fine  

Don't ask me why this whole topic is so tricky and somewhat confusing - that's just the way it is. But with the YYYYMMDD format, you should be fine for any version of SQL Server and for any language and dateformat setting in your SQL Server.

Conversion failed when converting date and/or time from character string

You'll need to divide your ORDER BY into multiple CASE statements:

ORDER BY 
    CASE WHEN @orderby = 0 THEN news_edits.[time] END DESC,
    CASE WHEN @orderby = 1 THEN news_edits.lastedit END DESC,
    CASE WHEN @orderby = 2 THEN news_edits.title END DESC

This is because single CASE statement requires that all branches have compatible data types. Since your character string in one CASE can't be converted to the date time returned from another CASE, you get the conversion error.

Conversion failed when converting date and/or time from character string and pull Quarter/Year from Date

You're saying that someone has inserted -1 into your column and it's tripping you up. While it's compounding the problem that this data should have been in some kind fo date column originally you can consider filtering out the undesirable values:

Select Distinct left(DateKey,4) as Year,
DatePart(QUARTER,cast(cast(DateKey as char(8)) as date)) As Quarter 
From Performance.WorkerPerformanceFact

WHERE DateKey BETWEEN 19700101 and 21991231 

Order By Year, Quarter

Adjust the WHERE clause to suit your needs of date range. I picked Jan 1 1970 and 31 dec 2199

But seriously consider changing the data type of the column now (the longer you leave it, the harder it gets..); I promise this won't be the last time this bites you in the ***; next time it will be another problem, like someone inserting 20200230 (there is no 30th of February) and you shouldn't keep trying to filter out the invalid values

The process of conversion to date doesn't have to be an instant one; you can add another DATE type column to the table, start filling it with a trigger from the DATEKEY column, switch apps to using the date column, and then drop the DATEKEY column later (to retain it for a while, populated from the true date column) all over the course of several revisions of the app

Getting Conversion failed error when converting date and/or time from character string

Storing datetime in varchar column is bad idea.Use appropriate column type when storing data.

Considering your question i assume there is invalid characters included in data like CR, LF or CR+LF.

try to replace them by

SELECT REPLACE(REPLACE(@str, CHAR(13), ''), CHAR(10), '')

Why error Conversion failed when converting date and/or time from character string appears

Try this:

SELECT 
    CASE 
        WHEN ISDATE(field_name)=1 THEN CONVERT(datetime, field_name, 103 ) 
        ELSE null 
    END
FROM your_table

I think you need to check that your date is correct and after that convert it.
You have a problem with your data.

Another solution:

Instead of convert use try_convert function:

try_convert(date,field_name,101) AS ChangeDate

SQL Error : Conversion failed when converting date and/or time from character string

I found the issue, it's a very very stupid mistake, there was one column with a text format I couldn't see it because I checked with length, but the SQL gives max length so I used group by and manually check dates until I found one column that's not correct in 1 m account records

Conversion failed when converting date with DATEADD

The order on Insert Into is Codigo, Fechtran, Fechaini, Fechaexp, IDCONF and in the Select the order is different, Fecha, Fecha2, Codigo, Fechtran, IDCONF.

So you're most likely inserting data to wrong columns.

---

Related Topics