?
Solved

DB2 LUW SQL Convert varchar field to date

Posted on 2011-05-13
2
Medium Priority
?
957 Views
Last Modified: 2012-05-11
Hi,

I have a varchar field and i am trying to convert this to a date and then add one year but am getting the error "The syntax of the string representation of a datetime value is incorrect"

This is the query that is giving the error

SELECT
RT_LOPDAT AS OPENINGDATE,
DATE(RT_LOPDAT) + 1 YEAR AS OPENINGDATEPLUSONEYEAR
FROM "SAPBDV"."/BI0/PPLANT"

RT_LOPDAT is a varchar and an example value is 20040329

How can i add a year to this value?

Thanks
0
Comment
Question by:smythsit
2 Comments
 
LVL 26

Accepted Solution

by:
Tomas Helgi Johannsson earned 2000 total points
ID: 35753185
        Hi!

You will need to convert/format the string to a valid date string format like 'DD.MM.YYYY' etc before you convert it to date.
Issue this sql to see what the default date string format is

select char(current date) from sysibm.sysdummy1;

http://www.ibm.com/developerworks/data/library/techarticle/0211yip/0211yip3.html

Hope this helps.

Regards,
    Tomas Helgi
0
 

Author Comment

by:smythsit
ID: 35753194
Thanks Tomas, just got it there using

DATE(TO_DATE(RT_LOPDAT,'yyyymmdd')) + 1 YEAR AS OPENINGDATEPLUS1YEAR,
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

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 (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?

850 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