?
Solved

SQL where clause using date format

Posted on 2014-12-19
18
Medium Priority
?
331 Views
Last Modified: 2014-12-22
I am trying to query a db that contains a field FILE_DATE and the format of the field values is '2014-02-02 00:00:00'.  I tried
select * from tbl.table where file_date = '2014-10-16 00:00:00 but I get an error message about data type. I tried CAST() the field but no luck.
0
Comment
Question by:szadroga
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 3
  • 2
  • +4
18 Comments
 
LVL 33

Expert Comment

by:Big Monty
ID: 40510038
try converting it to the ISO standard, that way the format won't matter:

SELECT * FROM tbl.table WHERE CONVERT(VARCHAR(20), FILE_DATE , 112) = '20140202';
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 40510039
whats the error

select * from tbl.table where file_date = '20141016'
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 40510041
>the format of the field values is '2014-02-02 00:00:00'
Is it a datetime, or a varchar where the values are stored as what you have there?
In SSMS, navigate to the table, then right-click:Design, find the column, and eyeball what's in the Data Type property.

>I tried select * from tbl.table where file_date = '2014-10-16 00:00:00' but I get an error message about data type
Is 'tbl.table' a typo, or is the schema named tbl and the table name table?

The syntax works on my 2012 box..
CREATE TABLE #tmp (dt datetime) 

INSERT INTO #tmp (dt) 
VALUES ('2014-10-16 00:00:00'), ('2014-01-01 00:00:00'), ('2014-12-31 00:00:00'), ('2014-10-31 00:00:00')

SELECT * FROM #tmp WHERE dt = '2014-10-16 00:00:00'

Open in new window

0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:szadroga
ID: 40510045
it is a DATE
0
 

Author Comment

by:szadroga
ID: 40510046
Big Monty - tried your suggestion but got  [Error Code: 936, SQL State: 42000]  ORA-00936: missing expression
0
 

Author Comment

by:szadroga
ID: 40510047
would it matter if it is an Oracle DB?
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 40510052
>it is a DATE
In that case, you can query just on the date.  Both of these work..

IF OBJECT_ID ('tempdb..#tmp') IS NOT NULL
   DROP TABLE #tmp

CREATE TABLE #tmp (dt date) 

INSERT INTO #tmp (dt) 
VALUES ('2014-10-16'), ('2014-01-01'), ('2014-12-31'), ('2014-10-31')

SELECT * FROM #tmp WHERE dt = '2014-10-16'

SELECT * FROM #tmp WHERE dt = '20141016'

Open in new window


>but got  [Error Code: 936, SQL State: 42000]  ORA-00936: missing expression
This question was posted in the SQL Server zone, so we assumed that you are using SQL Server.
Are you using Oracle, and looking for an Oracle answer?
0
 

Author Comment

by:szadroga
ID: 40510057
tried this
SELECT * FROM tablename WHERE file_date = '2014-12-16'; 

Open in new window

but got this  [Error Code: 1861, SQL State: 22008]  ORA-01861: literal does not match format string
0
 

Author Comment

by:szadroga
ID: 40510059
my mistake, i assumed it was SQL server
0
 

Author Comment

by:szadroga
ID: 40510062
it is an oracle db
0
 

Author Comment

by:szadroga
ID: 40510066
thx for your help
0
 
LVL 13

Accepted Solution

by:
Alexander Eßer [Alex140181] earned 2000 total points
ID: 40510131
If running on Oracle DB this really should be no problem at all:
select * from tbl.table where file_date = to_date('2014-10-16 00:00:00', 'yyyy-mm-dd hh24:mi:ss');

Open in new window


And please avoid implicit data type conversion at any cost, like "... where date_col = '2014-10-01 12:01:00' "
0
 
LVL 11

Expert Comment

by:Wilder1626
ID: 40510356
Alexander Eßer option work for me also.

Another option could probably be to pull based on today's date - number of days behind.
ex:
Select * from tbl.table where file_date = TO_DATE(TO_CHAR(SYSDATE,'YYYYMMDD'), 'YYYYMMDD') - 60; 

Open in new window


Or if you want to pull what's greater than
ex:
Select * from tbl.table where file_date > TO_DATE(TO_CHAR(SYSDATE,'YYYYMMDD'), 'YYYYMMDD') - 60; 

Open in new window

0
 
LVL 38

Expert Comment

by:Gerwin Jansen, EE MVE
ID: 40510629
Since you're not interested in the time, you could use TRUNC as well:
select * from tablename where TRUNC(file_date) = to_date('20141016', 'YYYYMMDD');

Open in new window

0
 
LVL 13

Expert Comment

by:Alexander Eßer [Alex140181]
ID: 40510678
@Wilder1626: Why the unnecessary double conversion?!?
Select * from tbl.table where file_date = trunc(sysdate) - 60; 

Open in new window

or
Select * from tbl.table where file_date = trunc(sysdate) - interval '60' day; 

Open in new window

This applies to the ">" and/or ">=" comparisons as well.

@Gerwin:
This will render any index on file_date unusable unless there is a FBI for this "trunc(file_date)"...
0
 
LVL 38

Expert Comment

by:Gerwin Jansen, EE MVE
ID: 40510714
@Alex - Wasn't aware this was a question about performance :)
0
 
LVL 13

Expert Comment

by:Alexander Eßer [Alex140181]
ID: 40510826
@Alex - Wasn't aware this was a question about performance :)
It was just meant as a side note. Many people take statements like these as is and afterwards they wonder why the CBO says "Ha ha, full table scan" ;-)
0

Featured Post

Enroll in August's Course of the Month

August's CompTIA IT Fundamentals course includes 19 hours of basic computer principle modules and prepares you for the certification exam. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question