TO_DATE or VARCHAR_FORMAT converts a character representation of a Date/Time
or Timestamp into a real timestamp.
If it is only for sorting IMHO it does not matter on whether the data is
character or timestamp.
... another way with SQL is (kind of substringing), but without converting
it into a date could be:
Right(CharDate, 4) concat left(CharDate, 4)
Mit freundlichen Grüßen / Best regards
Birgitta Hauser
Modernization ? Education ? Consulting on IBM i
IBM Champion since 2020
"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"
"Train people well enough so they can leave, treat them well enough so they
don't want to. " (Richard Branson)
"Learning is experience ? everything else is only information!" (Albert
Einstein)
-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Jon
Paris
Sent: Wednesday, 3 May 2023 23:26
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: Re: Date conversion
One simple way is to use the to_date function.
e.g. select to_date( '120623', 'MMDDYY') from sysibm.sysdummy1;
Replace '120623' with the column name and you should have no issues
sequencing it.
Jon P.
On May 3, 2023, at 4:59 PM, Howie, Bill <BHowie@xxxxxxxxxxxxxxxx> wrote:
Hello all,
I have what is surely a simple question but I can't seem to find an answer
to it. I have a work file that I've created that contains the output of the
DSPOBJD command for a particular library. This file has the field ODUDAT,
which is the date last used field. It's a character field in the MMDDYY
format. I'm using Run SQL Scripts in ACS to then query this file, but I'd
like to convert this date to YYYYMMDD format so that I can order the query
results by the most recent date. The only way I can find that comes close
to working so far is just to substring the ODUDAT field into the month, day,
and year portions and then play with it that way. I'm sure there's a more
elegant method that will just yield a YYYYMMDD field. Thoughts?
Any ideas on this would be greatly appreciated. Thanks!
Bill Howie[Related image]<https://www.hc-companies.com/>
Senior ERP Programmer/Analyst
Office: 330.486.5818 | Cell: 330.486.5818 | Toll Free: 800.225.7712
2450 Edison Blvd., Suite 3, Twinsburg, OH 44087
hc-companies.com<https://www.hc-companies.com/>
Leader in Horticultural Containers
[cid:e0b218dc-8ff8-41fd-81d2-b4ddf650420b]<
https://www.linkedin.com/company/
the-hc-companies> [cid:5a6b8cad-48ce-4933-8f87-19d7d96844ba]
<
https://www.facebook.com/HCCompanies/>
[cid:151d0fd6-bc45-4922-8e5f-cf1f42b0de8f]
<
https://www.instagram.com/hccompanies/>
[cid:19fc1ef7-7bff-4cae-87af-ec061a1c8770]
<
https://twitter.com/hc_companies>
Disclaimer
The information contained in this communication from the sender is
confidential. It is intended solely for use by the recipient and others
authorized to receive it. If you are not the recipient, you are hereby
notified that any disclosure, copying, distribution or taking action in
relation of the contents of this information is strictly prohibited and may
be unlawful.
This email has been scanned for viruses and malware, and may have been
automatically archived by Mimecast Ltd, an innovator in Software as a
Service (SaaS) for business. Providing a safer and more useful place for
your human generated data. Specializing in; Security, archiving and
compliance. To find out more visit the Mimecast website.
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx To
subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/midrange-l.
Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit:
https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/midrange-l.
Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.
midrange.com
