Conversion to Datetime Fails Only on Where Clause

Conversion to datetime fails only on WHERE clause?

If the conversion is in the WHERE clause it may be evaluated for many more records (values) than it would be if it appears in the projection list. I have talked about this before in different context, see T-SQL functions do no imply a certain order of execution and On SQL Server boolean operator short-circuit. Your case is even simpler, but is similar, and ultimately the root cause is the same: do not an assume an imperative execution order when dealing with a declarative language like SQL.

Your best solution, by a far and a large margin, is to sanitize the data and change the column type to a DATETIME or DATETIME2 type. All other workarounds will have one shortcoming or another, so you may be better to just do the right thing.

Update

After a closer look (sorry, I'm @VLDB and only peeking SO between sessions) I realize you have an EAV store with inherent type-free semantics (the attribute_value can bea string, a date, an int etc). My opinion is that your best bet is to use sql_variant in storage and all the way up to the client (ie. project sql_variant). You can coherce the type in the client, all client APIs have methods to extract the inner type from a sql_variant, see Using sql_variant Data (well, almost all client APIs... Using the sql_variant datatype in CLR). With sql_variant you can store multiple types w/o the problems of going through a string representations, you can use SQL_VARIANT_PROPERTY to inspect things like the BaseType in the stored values, and you can even do thinks like check constraints to enforce data type correctness.

Where clause fails when used with a 'convert' function

Use TRY_CAST if you're unsure if the value can actually be converted.

But be advised that the fact that you are receiving this error probably points to a problem with certain records in your column. Only use TRY_CAST if you only care about getting the records where the varchar column can actually can be converted to a datetime.

SELECT
    [screen_date]
FROM
    [d8003]
WHERE
    TRY_CAST([screen_date] AS DATETIME) BETWEEN '6/1/2018' AND '12/31/2018'

Why does using CONVERT(DATETIME, [date], [format]) in WHERE clause take so long?

Going to summarise my comments here, as they are "second class citizens" and thus could be removed.

Firstly, the reason your query is slow is because of theCONVERT on the column DATE in your WHERE. Applying functions to a column in your WHERE will almost always make your query non-SARGable (there are some exceptions, but that doesn't make them a good idea). As a result, the entire table must be scanned to find rows that are applicable for your WHERE; it can't use an index to help it.

The real problem, therefore, is that you are storing a date (and time) value in your table as a non-date (and time) datatype; presumably a (n)varchar. This is, in truth, a major design flaw and needs to be fixed. String type values aren't validated to be valid dates, so someone could easily insert the "date" '20210229' or even 20211332'. Fixing the design not only stops this, but also makes your data smaller (a date is 3 bytes in size, a varchar(8) would be 10 bytes), and you could pass strongly typed date and time values to your query and it would be SARGable.

"Fortunately" it appears your data is in the style code 112, which is yyyyMMdd; this at least means that the ordering of the dates is the same as if it were a strongly typed date (and time) data type. This means that the below query will work and return the results you want:

SELECT TOP 10 * --Ideally don't use * and list your columns properly
FROM dbo.[Table]
WHERE [DATE] >= '20210704' AND [DATE] < '20210705'
ORDER BY {Some Column};

Converting Date in Where clause in SQL

Because you have no other choice then to use a varchar as a date, then I would convert the one GETDATE() value to a varchar instead of converting all the rows to a date. It's possible because they are stored as YYYYMMDD. I think that would be bettter for performance (for sure if you have an index on this date column).

WHERE MyDate > CONVERT(varchar(10),dateadd(d,-30,getdate()),112)

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 - SQL Server error

Well , as mentioned in the comments you probably have other tables in your database that does not follow the same format as tableA<DateFormat> , so you need to try to filter only them .

You can use ISDATE combined with CASE EXPRESSION to make sure the SUBSTRING is indeed in a date format:

DECLARE @cutoffDate datetime = CONVERT(DATETIME, DATEADD(YEAR,-1,GETDATE()), 112)

SELECT 'DROP TABLE "' + TABLE_NAME + '"' AS 'Command'
    FROM INFORMATION_SCHEMA.TABLES
    WHERE (TABLE_NAME LIKE 'tableA%' OR TABLE_NAME LIKE 'tableB%')
    AND CASE WHEN ISDATE(SUBSTRING(TABLE_NAME, 7, 8)) = 1 
             THEN (CONVERT(DATETIME, SUBSTRING(TABLE_NAME, 7, 8), 112)
             ELSE getdate()
        END < @cutoffDate
    ORDER BY Command DESC

Varchar-to-Datetime conversion error - fine in SELECT but breaks in WHERE

Try adding condition like this : WHERE (CASE WHEN ISDATE(ApptDateTime) = 1 THEN ApptDateTime ELSE NULL END) < GETDATE()

I faced same issue before couple of days and solved by adding CASE.
Not clear about how SQL executes where condition.
I also need to find actual reason for the same. If I find anything, I will post in same post.

Edit: If you see in execution plan of your actual query, it will show you ApptDateTime < GETDATE() before ISDATE(ApptDateTime) = 1 which causing the error.

Reason is WHERE clause doesn't follow short-circuit rule. Ref: Is the SQL WHERE clause short-circuit evaluated?

How to fix SQL error when querying based on date value in a where statement?

Many times the problem is with the time zone setting. Specify in other formats like mm/dd/yyyy (usually works).

If you need to convert your input the you can try looking into the CONVERT method. Syntax is

CONVERT(VARCHAR,@your_date_Value,103)

CONVERT(VARCHAR, '12/30/2013', 103)

The finishing 103 is the datetime format.

Refer this link for conversion formats and further reading. https://www.w3schools.com/sql/func_sqlserver_convert.asp

How to convert Datetime to Date and use in Where clause?

You can use cast(CreatedDate as Date) to convert. But I don't think you need to convert in this case to compare.

Just do:

WHERE CreatedDate between '2016-06-01' and '2016-06-31'
should be fine

---

Related Topics