RE: ISO date to numeric in SQL

UDF are easy...

create function CvtDateToNumISO(indte date) returns numeric(8,0)  
language SQL                                                           
not fenced                                                             
deterministic no external action                                       
specific CvtDateToNumISO                                               
returns null on null input                                             
contains SQL                                                           
set option datfmt=*ISO                                                 
begin                                                                  
  return( ( year(inDte) * 10000) +                                     
          ( month(inDte) * 100) +                                      
          ( day(inDte) ) );                                            
end;            

This returns an 8 digit numeric YYYYMMDD, since you apparently want 6
digit numeric, you'd need to modify it.

HTH,
                                                      

Charles Wilt
--
iSeries Systems Administrator / Developer
Mitsubishi Electric Automotive America
ph: 513-573-4343
fax: 513-398-1121
  

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx 
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of 
AGlauser@xxxxxxxxxxxx
Sent: Thursday, September 07, 2006 3:37 PM
To: midrange-l@xxxxxxxxxxxx
Subject: ISO date to numeric in SQL

Just a quick SQL question.  I found lots of way to convert 
numeric to real 
dates, but nothing for date to numeric on searching the archives.
Here is what I did:

YYMMDD = MOD(YEAR(ISODATE),100)*10000+MONTH(ISODATE)*100+DAY(ISODATE)

I also had some "0001-01-01" to zero handling.  It worked okay, but I 
didn't have many records, and it was ugly to read.  Is there a more 
efficient/cleaner method for doing this?  I know I could 
build a UDF, but 
for one I've never done so, and for another that might rock the "shop 
standards" boat a bit, which isn't necessarily worth it yet 
for the amount 
of pure SQL that we use.

TIA,
Adam

##############################################################
#######################
Attention:
The above message and/or attachment(s) is private and 
confidential and is intended 
only for the people for which it is addressed. If you are not 
named in the address 
fields, ignore the contents and delete all the material. 
Thank you. Have a nice day.

For more information on email virus scanning, security and content
management, please contact administrator@xxxxxxxxxxxx
##############################################################
#######################
-- 
This is the Midrange Systems Technical Discussion 
(MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.