How to optimize an Oracle query that has to_char in where clause for date

Error processing SSI file

Answers

  1. Remi

    • 2015/10/25

    This works:

    WHERE             
    create_date >= to_timestamp('04/10/2010 00:00:00.000000','MM/DD/YYYY HH24:MI:SS.FF') 
    AND 
    create_date <= to_timestamp('04/10/2010 23:59:59:123000','MM/DD/YYYY HH24:MI:SS.FF') 
    
  2. Zeke

    • 2019/1/17

    Hi All, i am using sql server 2005. I have the following table EmployeeId is bigint and AttTime column is datetime EmployeeId AttTime 101 

  3. Hendrix

    • 2019/3/12

    I actually used the mirror image of this solution because in my problem, the timestamp column is the one that will be searched many times over, so I first converted the date format into timestamp, created your same +1 variable, and then did the where <, where > inequality to make sure that the two dates are from 0:00 to 1 day later

  4. Atreus

    • 2016/4/11

    We use SQL Between operator in the Where clause for selecting a range of values. Example 2: SQL Between operator with Date Range.

  5. Kylan

    • 2015/11/14

    Of course this doesn't work:

    WHERE            
        create_date >= to_timestamp('04/10/2010 00:00:00.000000','MM/DD/YYYY HH24:MI:SS.FF')
    AND
        create_date <= to_timestamp('04/10/2010 00:00:00.000000','MM/DD/YYYY HH24:MI:SS.FF')
    

    Because that would only return rows where the create_date is 4/10/2010 12:00 AM exactly!

    If you want to get all rows where create_date occurs any time on the day of 4/10/2010, use this:

    WHERE            
        create_date >= to_timestamp('04/10/2010 00:00:00.000000','MM/DD/YYYY HH24:MI:SS.FF')
    AND
        create_date < to_timestamp('04/11/2010 00:00:00.000000','MM/DD/YYYY HH24:MI:SS.FF')
    

    OR if you prefer:

    WHERE create_date BETWEEN to_timestamp('04/10/2010 00:00:00.000000','MM/DD/YYYY HH24:MI:SS.FF')
                          AND to_timestamp('04/10/2010 23:59:59.999999','MM/DD/YYYY HH24:MI:SS.FF')
    

    By the way, when you want to represent midnight, you can leave all the other parts out. So you could just say:

    WHERE            
        create_date >= to_timestamp('04/10/2010','MM/DD/YYYY')
    AND
        create_date < to_timestamp('04/11/2010','MM/DD/YYYY')
    
  6. Anders

    • 2018/8/13

    Using SQL Server CE 3.5 with the Express version of VisualBasic2008 in VisualStudio2008. Have a table with a DateTime type column that holds the start date and time for events. I give the user the ability to select records (rows) based on the DateTime field (column) by entering a date, a time or a date and time into a TextBox.

  7. Russo

    • 2015/6/11

    Can we use if condition in where clause in SQL?

  8. David

    • 2018/1/8

    It may be DATE or VARCHAR2 and there is no way to know by just looking at it. Run describe table_name (where table_name is the name of the table that contains this column) and see what it says. If it's a VARCHAR2 then you need to convert it to a date as well. Use the proper format model: 'dd-Mon-rr'.

  9. Charlie

    • 2017/12/22

    In your first query, you are doing a character comparison rather than a date comparison, which should not be producing correct results.

    For example, using your logic, 01/02/2009 will be greater than 01/01/2010 because the day component '02' is greater than the day component '01' when comparing characters and the year will never be evaluated.

  10. Aayan

    • 2016/10/24

    SQL Server comes with the following data types for storing a date or a date/time value in the database: DATE - format YYYY-MM-DD. DATETIME - format: YYYY-MM-DD HH:MI:SS. SMALLDATETIME - format: YYYY-MM-DD HH:MI:SS. TIMESTAMP - format: a unique number.

  11. Kade

    • 2018/1/15

    Hi All, i am using sql server 2005. I have the following table EmployeeId is bigint and AttTime column is datetime EmployeeId AttTime 101 2015-09-19 11:15:37.000 102 2015-09-19 11:05:37.000 103 201

  12. Nixon

    • 2020/6/26

    I see a bad performance when I run the query with the above condition. Is there any other/better way to deal with dates in long running queries? I tried "and" 

  13. Koroveshi

    • 2015/1/11

    I get 529 rows but in 255.59 seconds! Which is because I guess I am doing TO_CHAR on EACH record.

    If you were to generate an execution plan for your first query ...

    explain plan for 
    SELECT bunch,of,stuff,create_date
    FROM myTable
    WHERE TO_CHAR (create_date,'MM/DD/YYYY)' >= '04/10/2010'
    AND TO_CHAR (create_date, 'MM/DD/YYYY') <= '04/10/2010'
    /
    

    ... you would see that it does a full table scan. That's because the to_char() prevents the use of your index on CREATE DATE.

    You don't say how long it took to return the results when you ran ...

    SELECT bunch,of,stuff,create_date
    FROM myTable
    WHERE             
    create_date >= to_timestamp('04/10/2010 00:00:00.000000','MM/DD/YYYY HH24:MI:SS.FF') 
    AND 
    create_date <= to_timestamp('04/10/2010 23:59:59:123000','MM/DD/YYYY HH24:MI:SS.FF')
    /
    

    ... but I expect it was way closer to 0.14 seconds than 4 minutes.

  14. Calum

    • 2018/4/17

    I have a table that contains about 49403459 records. I want to query the table on a date range. say 04/10/2010 to 04/10/2010. However, the dates are stored in the table as format 10-APR-10 10.15.06.

  15. Warren

    • 2017/5/3

    Can we use between for dates in SQL?

  16. Stanley

    • 2016/11/9

    The TO_DATE function in where clause. SQL> SQL> -- create demo table SQL> create table Employee( 2 ID VARCHAR2(4 BYTE) NOT NULL, 3 First_Name VARCHAR2(10 BYTE), 4 Last_Name VARCHAR2(10 BYTE), 5 Start_Date DATE, 6 End_Date DATE, 7 Salary Number(8,2), 8 City VARCHAR2(10 BYTE), 9 Description VARCHAR2(15 BYTE) 10 ) 11 / Table created.

  17. Ulises

    • 2018/12/20
    SELECT bunch,of,stuff,create_date
      FROM myTable
     WHERE create_date >= to_date('04/10/2010','MM/DD/YYYY')
       AND create_date < to_date('04/11/2010','MM/DD/YYYY')
    

    The date 04/10/2010 includes all the date values from midnight on the 10th until 11:59:59 PM, so getting everything less than the 11th will cover all the bases. An alternative is to ensure data in myTable has the CREATE_DATE field truncated on data entry; I prefer to do that for DATE fields, and if I care about the time components, I use TIMESTAMPs.

  18. Justin

    • 2015/7/23

    How do I query a date in SQL Server?

  19. Rhys

    • 2021/9/22

    You just need to use boolean logic (or rather the ternary logic that SQL uses) and rewrite it: WHERE DateDropped = 0 AND (@JobsOnHold = 1 AND DateAppr >= 0 OR (@JobsOnHold <> 1 OR @JobsOnHold IS NULL) AND DateAppr <> 0)

  20. Albert

    • 2018/10/1

    Sql query to get data between two dates oracle. Select the Data from the Table Between Two Date Ranges, I am using oracle database. i want to execute one 

  21. Isaiah

    • 2020/1/14

    Your 1st query is doing a string comparison with wrong results. Your 2nd query needs to be:

    WHERE create_date >= TRUNC(to_date('04/10/2010','MM/DD/YYYY'))

    or add hh:mi:ss to the predicate. It's not working simply 'cos you're formatting the date in a different way to what Oracle expects.

  22. Simeon

    • 2016/10/25

    The main purpose is still to find a shorter way of extracting minutes, after doing subtraction between two timestamps. I hope I make sense. – Carnal Feb 2 '17 at 5:21

  23. Quinn

    • 2021/5/9

    As the title says, I want to find a way to check which of my data sets are past 6 months from SYSDATE via query. SELECT * FROM OrderArchive WHERE OrderDate <= '31 Dec 2014'; I've tried the following but it returns an error saying my date format is wrong.

  24. Eric

    • 2016/9/19

    Second of all, you need to be aware that SQL Server 2005 DATETIME always includes a time. If you check for exact match with just the date part, you'll get only rows that match with a time of 0:00:00 - nothing else.

  25. Denver

    • 2015/6/7

    Explicit where conversions are visible to the user and they are performed using CAST or CONVERT functions or other tools. In this article, we will 

  26. Jaden

    • 2016/6/26

    SELECT CAST(GETDATE() AS DATE) As [Date] We have used date condition something like shown below, where CAST (Emp.DateOfBirth AS DATE) >= CAST (@From_Birth_Date AS DATE) return all the records from employee table whose date of birth is equal to or greater than equal to From_Birth_Date.

  27. Kenneth

    • 2019/11/17

    select * from dbo. March2010 A where A. Date >= 2005; ( 2010 minus 4 minus 1 is 2005 Converting it to a proper datetime , and using single quotes will fix this issue.)

  28. Tyson

    • 2020/2/23

    SQL Date Data Types. MySQL comes with the following data types for storing a date or a date/time value in the database: DATE - format YYYY-MM-DD; DATETIME - format: YYYY-MM-DD HH:MI:SS; TIMESTAMP - format: YYYY-MM-DD HH:MI:SS; YEAR - format YYYY or YY; SQL Server comes with the following data types for storing a date or a date/time value in the

  29. Bellini

    • 2021/10/9

    to_timestamp() You need to use to_timestamp() to convert your string to a proper timestamp value: to_timestamp('12-01-2012 21:24:00', 'dd-mm-yyyy hh24:mi:ss') to_date() Example. Note.

  30. Judson

    • 2015/3/17

    Oracle uses blank-padded comparison semantics only when both values in the comparison are either expressions of datatype CHAR, NCHAR, text literals, or values returned by the USER function. Nonpadded Comparison Semantics. Oracle compares two values character by character up to the first character that differs.

  31. Santos

    • 2016/11/28

    Unable to fetch records by using 'where' clause for a date type field in sql. Post by​: Vinod I'm using oracle as database. On Toad, i'm select * from exchange_rate where updated_date = to_date( '12/16/2010','mm/dd/yyyy')

  32. Ward

    • 2015/4/2

    How can I get the year and month of a date in the where clause using Oracle. I used to be working with SQL server and it was as simple as YEAR(FIELDNAME) and MONTH(FIELDNAME).

  33. Mordechai

    • 2015/8/10

    Basics Get Started with BigQuery and PopSQL in 5 Minutes Accessing BigQuery Public Datasets BigQuery Data Types · Database Management How to Create a​ 

  34. Noe

    • 2015/9/18

    Extract the year part of the reference date and use normal arithmetic to add 10, 20, and 40 to it: 6. The following query shows two ways to determine the date for Christmas two years hence. 7. Performing Leap Year Calculations: 8. Another way to compute a year's length is to compute the date of the last day of the year and pass it to DAYOF: 9.

  35. Azariah

    • 2016/7/18

    SQL Dates. Note, The most difficult part when working with dates is to be sure that the format of the date you are trying to 

  36. Jackson

    • 2016/9/28

    Between includes the last value whereas when you use datevalue >= firstdate and < the last date + 1 day you get all the rows that occurred on the last day at a specific time. If your dates don't have times (all times are at midnight on the date) or you're using a SQL Server 2008 DATE data type field then it shouldn't matter which one you use.

  37. Tripp

    • 2016/10/3

    Using DateTime column in where clause Forum – Learn more on I do all my sql date processing using yyyy-mm-dd format even if it requires a 

  38. Austin

    • 2018/7/16

    In this article i am going to explain how you can pass date range condition where clause in SQL server and also show you how you can filter record from particular table based on condition in where clause in SQL server as well as also explains how you can convert format of date in dd/MM/yyyy or yyyy-MM-dd in SQL server.

  39. Calum

    • 2020/8/7

    Hi All, i am using sql server 2005. I have the following table EmployeeId is bigint and AttTime column is datetime EmployeeId AttTime 101 

  40. Remi

    • 2019/5/11

    The SQL WHERE Clause The WHERE clause is used to filter records. The WHERE clause is used to extract only those records that fulfill a specified condition.

  41. Basile

    • 2016/5/27

    In SQL 2008+, CAST(foo AS date) is sargable, along with a few other manipulations. Look at the execution plans in the sqlfiddle. SQL Fiddle 

  42. De Rosa

    • 2017/10/25

    This article contains examples of converting a datetime value to a date value in SQL Server. The obvious consequence of converting a datetime value to date is that you lose the time portion. However, one benefit is that you reduce the storage size from 8 bytes down to 3 bytes.

  43. Victor

    • 2020/5/5

    Admittedly, not all processes are so time-critical that a millisecond here or there makes a difference, but since timestamp is no more cumbersome 

  44. Aaron

    • 2021/2/12

    Under those conditions, the comparison in the WHERE clause occurs in different ways for nonindexed and indexed lookups and leads to different results: If there is no index or the optimizer cannot use it, comparisons occur in the session time zone.

  45. Diaz

    • 2018/10/31

    How to filter on date values in SQL using the SQL WHERE clause.The full Introduction to Duration: 3:36 Posted: Mar 3, 2018

  46. Kashton

    • 2020/7/17

    I used this statement to retrive all the data they have value 3/29/2007 in the field of dateOfSold. When i try this statement this return empty record set. dateOfSold field is in access table sales. fieldtype is date/time. plese help me

Comments are closed.

More Posts