SQL where clause using date format

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.
szadrogaAsked:
Who is Participating?
 
Alexander Eßer [Alex140181]Connect With a Mentor Software DeveloperCommented:
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
 
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
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
 
Aneesh RetnakaranDatabase AdministratorCommented:
whats the error

select * from tbl.table where file_date = '20141016'
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>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
 
szadrogaAuthor Commented:
it is a DATE
0
 
szadrogaAuthor Commented:
Big Monty - tried your suggestion but got  [Error Code: 936, SQL State: 42000]  ORA-00936: missing expression
0
 
szadrogaAuthor Commented:
would it matter if it is an Oracle DB?
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>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
 
szadrogaAuthor Commented:
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
 
szadrogaAuthor Commented:
my mistake, i assumed it was SQL server
0
 
szadrogaAuthor Commented:
it is an oracle db
0
 
szadrogaAuthor Commented:
thx for your help
0
 
Wilder1626Commented:
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
 
Gerwin Jansen, EE MVETopic Advisor Commented:
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
 
Alexander Eßer [Alex140181]Software DeveloperCommented:
@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
 
Gerwin Jansen, EE MVETopic Advisor Commented:
@Alex - Wasn't aware this was a question about performance :)
0
 
Alexander Eßer [Alex140181]Software DeveloperCommented:
@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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.