RE: SQL string to date conversion

No need to install iDate, you can also write your own UDF, that handles invalid dates and multiple date formats:

Create the UDF, save the SQL Script and then run it on any machine you need it, once!

And voilà you can use it where ever you need it. A UDF can be used like any SQL scalar function.



CREATE OR REPLACE FUNCTION YOURSCHEMA.CVTNUM2DATEFMT (

PARDATENUM DECIMAL(8, 0) DEFAULT 99991231 ,

PARFORMAT VARCHAR(4) DEFAULT 'ISO' )

RETURNS DATE

LANGUAGE SQL

SPECIFIC YOURSCHEMA/CVTNUM2DATF

DETERMINISTIC

MODIFIES SQL DATA

CALLED ON NULL INPUT

SET OPTION DBGVIEW = *SOURCE

BEGIN

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION RETURN DATE ( '8888-12-31' ) ;



CASE WHEN UPPER ( PARFORMAT ) = 'ISO'

THEN RETURN DATE ( DIGITS ( PARDATENUM ) CONCAT '000000' ) ;

WHEN UPPER ( PARFORMAT ) = 'EUR'

THEN RETURN DATE ( TIMESTAMP_FORMAT ( DIGITS ( PARDATENUM ) , 'DDMMYYYY' ) ) ;

WHEN UPPER ( PARFORMAT ) = 'USA'

THEN RETURN DATE ( TIMESTAMP_FORMAT ( DIGITS ( PARDATENUM ) , 'MMDDYYYY' ) ) ;

WHEN UPPER ( PARFORMAT ) = 'YMD'

THEN RETURN DATE ( TIMESTAMP_FORMAT ( RIGHT ( DIGITS ( PARDATENUM ) , 6 ) , 'YYMMDD' ) ) ;

WHEN UPPER ( PARFORMAT ) = 'DMY'

THEN RETURN DATE ( TIMESTAMP_FORMAT ( RIGHT ( DIGITS ( PARDATENUM ) , 6 ) , 'DDMMYY' ) ) ;

WHEN UPPER ( PARFORMAT ) = 'MDY'

THEN RETURN DATE ( TIMESTAMP_FORMAT ( RIGHT ( DIGITS ( PARDATENUM ) , 6 ) , 'MMDDYY' ) ) ;

WHEN UPPER ( PARFORMAT ) = 'CYMD'

THEN RETURN DATE ( DIGITS ( DEC ( PARDATENUM + 19000000 , 8 , 0 ) ) CONCAT '000000' ) ;

ELSE RETURN DATE ( '7777-12-31' ) ;

END CASE ;

END ;





Mit freundlichen Grüßen / Best regards



Birgitta Hauser



"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)





-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of T. Adair
Sent: Dienstag, 26. März 2019 19:34
To: midrange-l@xxxxxxxxxxxxxxxxxx
Subject: Re: SQL string to date conversion



idate is certainly doable, but I was looking for a technique that I could use on any system, whether it has idate or not. We currently don't have it, thought I like the idea of putting it under the microscope to see how it works.



I really like Doug's statement - in this case I was able to go with it.

Rob is correct - this is the way to go when feasible.



I very much appreciate Birgitta's input. We store most of our 'dates'

in MMDDYY format, so there will probably be times that I need to use this technique.



As I have said before, you people are awesome!



Thanks again.



~TA~





On 3/26/2019 1:19 PM, T. Adair wrote:

Thanks to all who replied. I now have some shiny new tools in my toolbox.

~TA~

--

This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: <mailto:MIDRANGE-L@xxxxxxxxxxxxxxxxxx> MIDRANGE-L@xxxxxxxxxxxxxxxxxx To subscribe, unsubscribe, or change list options,

visit: <https://lists.midrange.com/mailman/listinfo/midrange-l> https://lists.midrange.com/mailman/listinfo/midrange-l

or email: <mailto:MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx> MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx

Before posting, please take a moment to review the archives at <https://archive.midrange.com/midrange-l> https://archive.midrange.com/midrange-l.



Please contact <mailto:support@xxxxxxxxxxxx> support@xxxxxxxxxxxx for any subscription related questions.



Help support midrange.com by shopping at amazon.com with our affiliate link: <https://amazon.midrange.com> https://amazon.midrange.com