Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

SQL where clause using date format

Posted on 2014-12-19
18
Medium Priority
?
407 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
  • 7
  • 3
  • 2
  • +4
17 Comments
 
LVL 34

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
Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

 

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 14

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 14

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 14

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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
Suggested Courses

577 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