Solved

SQL where clause using date format

Posted on 2014-12-19
18
298 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
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering 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

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…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
Via a live example, show how to take different types of Oracle backups using RMAN.

828 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