?
Solved

SQL: Convert VARCHAR to Date Format

Posted on 2005-05-02
6
Medium Priority
?
763 Views
Last Modified: 2012-05-05
Hi

I'm working with a table that receives data from a LDAP, and since it is character based, the date/time fields are imported in as type VARCHAR.

I'm trying to manipulate data with different data ranges and having problems converting VARCHAR to date format.

Examples of 'time' field in VARCHAR:
2002-05-15-19.05.19.000000
2002-05-15-19.05.45.000000
2002-05-15-19.06.06.000000
2002-05-15-19.15.37.000000
2002-05-15-19.18.50.000000
2002-05-15-19.23.17.000000

For some reason I am able to use the function date(time) in the select, in another words, I can do:
select time, date(time) from table 1
and receive valid conversions:
2000-02-14-11.21.00.000000      2000-02-14
2000-02-14-11.26.00.000000      2000-02-14
2000-02-14-11.29.00.000000      2000-02-14
2000-02-14-11.41.00.000000      2000-02-14

However the problem occurs when I apply this to the where clause:
select *
from table1
where date(time) > '2005-4-30';

The following error is returned:
[IBM][CLI Driver][DB2/NT] SQL0180N  The syntax of the string representation of a datetime value is incorrect.  SQLSTATE=22007

Any advice is appreciated. Thanks.
0
Comment
Question by:tptech
3 Comments
 
LVL 46

Accepted Solution

by:
Kent Olsen earned 172 total points
ID: 13911896


DB2 is funny this way.  You'll need an intermediate cast.

select cast (time as date) as comparedate, <itemlist>
from table1
where comparedate > '2005-4-30';


A pain, but that's DB2.

Kent
0
 
LVL 13

Assisted Solution

by:ghp7000
ghp7000 earned 164 total points
ID: 13912233
um, no casting needed, db2 can extract the date without casting. If you are doing your sql from command line, put the entire statement in double quotes, but you do not need to cast the column, your problem lies elsewhere
0
 
LVL 18

Assisted Solution

by:Dave Ford
Dave Ford earned 164 total points
ID: 13913742
With my date-format set to *ISO, your original query works for me.

Maybe, try something like:

select
   *                            
from
   deleteme
where
   date(aVC) = date('2000-02-14');

HTH,
DaveSlash
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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

November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Is your organization moving toward a cloud and mobile-first environment? In this transition, your IT department will encounter many challenges, such as navigating how to: Deploy new applications and services to a growing team Accommodate employee…

612 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