Linker IT Software
Google
Web www.oraxcel.com
menubar-top-links menubar-top-rechts
Home Help Search Login
Welcome, Guest. Please Login.
SQL*XL: Database to Excel bridge litLIB: Excel power functions pack ExcelLock: Locking and securing your valuable Excel spreadsheets encOffice: Protect your Excel file easy and safe encOffice: Protect your Excel file easy and safe
Pages: 1
Using dates in SQL (Read 8566 times)
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Using dates in SQL
21.11.05 at 10:50:31
 
Elmar from Germany asked the following question today.
 
Question:
How do I use dates in SQL correctly?
 
When I execute the following SQL it works fine:  
select * from mytable where mydatecol = '2005-07-25 00:00:00' ;
 
When I use parameters SQL*XL says the value in the cell is not a date. I typed dates with various date formats in my cell E2. I have also tried to set the data type of the parameter explicitly to date.
select * from mytable where mydatecol > :E2;
 
Answer:
The first tip I would like to give is to not rely on implicit conversions but to always use explicit conversions.
An implicit conversion is when you type: select * from mytable where mydatecol = '2005-07-25 00:00:00' ;
The database will see the date as a string (it is between quotes) and the database will see that mydatecol is of type date. It will then try to cast the string into a date using the date formats it will use by default. Note that this is on the database server and not on your client PC. The default date format can be different on the server as on your PC.
 
Be explicit in conversions. Use:
select * from mytable where  mydatecol = to_date('2005-07-05','yyyy-mm-dd');
 
Now when you use parameters you are getting the error that your cell value is not a date. This check is done by SQL*XL on your client PC. When you set a parameter data type to date, SQL*XL will try to read the contents of the cell and it will try to store it as a date. Clearly that failed for you. You need to use the format used on your PC to type a date. If Excel thinks it is a date SQL*XL will also use it as a date.  
Executing the select statement with a parameter can still lead to implicit conversions:  
select * from mytable where mydatecol > :E2;
 
If you make the parameter E2 a date no implicit conversions are done. However if you make E2 a string then the conversion is done by the database server. This could potentially lead to the wrong interpretation. Consider for example a PC running in German (default date day-month-year) with an american database server (default date month-day-year). You would think that the date string '01-02-2005'  would mean the first of Februari on your German desktop however the server interprets it as the second of Janurary.
 
My advise is to always be explicit with conversions. Do NEVER rely on your database server to convert it for you. When you use parameters use the appropriate data type in the parameters dialog to be sure no implicit conversions happen. You can also choose to use substitution variables (also setup in the parameters dialog). These are string variables by definition so you are always sure the value is a string. You need to explicitly convert these in the SQL yourself.
 
Example of substitution variables:
select * from mytable where  mydatecol = to_date('&mydate','yyyy-mm-dd');
 
If you would execute it like this it would be an implicit conversion again:
select * from mytable where  mydatecol = &mydate;
Back to top
 
« Last Edit: 21.11.05 at 11:06:00 by Gerrit-Jan Linker »  

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Re: Using dates in SQL
Reply #1 - 15.06.07 at 10:58:31
 
Another illustration on how implicit conversions can mess up:
 
Consider this select statement:
 
select datum, to_char(datum), to_date(datum)
from
( select to_date('01-06-2050','dd-mm-yyyy') datum from dual  
) a
 
01/06/2050   01-06-50   01/06/1950
 
Explanation:
 
A simpel date is created by explicitly converting the 2050 date. The date column is given an alias: datum (the Dutch word for date...)
 
Selecting the date value shows a date of 2050. The problem is when doing a to_char on the date without using a date format. Oracle will convert it to 50 using the standard YY date format. When converting that again to a date Oracle will assume it is 1900 and will make it 1950.  
 
This is what happens when you use the to_date around the date value. The to_date function requires a varchar input parameter. We supplied a date so Oracle will first convert our date to a varchar. In doing so it will convert the 2050 date to 50 as we illustrated above. Then the to_date function will convert this varchar to a date. It will assume the year 1900 also as illustrated above. Therefore a to_date around a date in 2050 will result in a date in 1950.
 
Be aware of this!
Back to top
 
 

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Re: Using dates in SQL
Reply #2 - 21.06.07 at 10:24:52
 
Another question about date formats:
 
Question:
Is there a trick to format the DATE the way we want when writing a SQL statement? For instance, in my daily work, I say "select movies from all_movies where opening_date>'01-JAN-2007';". This does not work in your application and all movies, regardless of the date, are returned. I figured out your app needs me to say "opening_date>'2007-01-01" or it won't take effect. Any ideas as to how to note the date the way I am used to instead of having to use this new format? Thank you.
 
Answer:
The crucial distinction to make it that between format and value.
 
Then typing a date value is it always necessary to format the date in a particular way. Is this is obvious to you perhaps the following isn't. When the database tries to make sense of your SQL string it sees your formatted date and it needs to convert it back to a date. It is there where the trouble is.
 
The database uses a default date format that is set in the so called NLS settings (Natural Language Support). In some countries as in my country The Netherlands for example, the date is formatted as day-month-year. In the USA you use month-day-year and I believe that they use year-day-month in Poland (correct me if I am wrong!).
 
To ensure you accurately communicate parameter values in SQL to your database you need to always apply explicit data conversion and not rely on the implicit data conversions.  
 
In the case you use a string literal no conversion is needed as the string resides in the SQL string and the database will see the text as a string too. Example:
select * from all_movies where title = 'Rocky 5';
 
In the case you use a number literal you need to make a conversion as in the SQL string the number is actually formatted as a string and the database needs to convert that string into a number. You need to take care here with your decimal separator. In the Netherlands we use a , as decimal separator and in the US or UK people use a dot (.). Typing 1.000 here is actually the number 1000 whereas the number 1,000 is just one. Example of explicit number conversions in Oracle:
select * from all_moviews where movie_id = to_number(123);
 
In the case you use a date literal conversions are needed too. In Oracle you can use the to_date function where you specify a date format string. Example:
select * from all_moviews where release_date = to_date('01-12-2006','dd-mm-yyyy')
Back to top
 
 

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Pages: 1