Solved

SQL where clause using date format

Posted on 2014-12-19
18
307 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 65

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 65

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 500 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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

726 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