Re: m/d/yyyy string to yyyymmdd decimal in SQL

[Alan Campin <alan0307d@xxxxxxxxx>]

You could use my iDate User Defined Function (www.think400.dk/downloads.htm)
to convert to Date Data Type and then you could do anything you want with
it.

On Fri, May 4, 2018 at 3:05 PM Dan <dan27649@xxxxxxxxx> wrote:

> My input date, from a spreadsheet that comes from the a client, is a string
> using the format (m)m/(d)d/yyyy, (m)m represents that there could be one or
> two digits and ditto for (d)d, and I need to convert it to a decimal
> yyyymmdd.
>
> The following does it, but it seems to me that there should be an easier
> way, utilizing less than four functions to do the conversion.  Is there?
>
> select BI_DOSA,
>        Dec( Replace(
>           Cast( Date(TIMESTAMP_FORMAT( BI_DOSA, 'MM/DD/YYYY'))
>                 as Char(10)),
>                     '-', ''),8)
>        from  CA8560BIP
>
> BI_DOSA            DEC
>  8/21/2017  20,170,821
>  8/21/2017  20,170,821
>  9/06/2017  20,170,906
>  9/06/2017  20,170,906
>  9/07/2017  20,170,907
>  1/19/2017  20,170,119
> 10/31/2017  20,171,031
> 10/5/2016   20,161,005
>  2/13/2018  20,180,213
>  1/05/2018  20,180,105
>  1/05/2018  20,180,105
>  3/01/2018  20,180,301
>  2/21/2018  20,180,221
>  2/26/2018  20,180,226
>  2/28/2018  20,180,228
>  8/10/2016  20,160,810
> 11/14/2016  20,161,114
>  3/23/2017  20,170,323
> 11/9/2016   20,161,109
>
> - Dan
> --
> 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: https://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at https://archive.midrange.com/midrange-l.
>
> Please contact support@xxxxxxxxxxxx for any subscription related
> questions.
>
> Help support midrange.com by shopping at amazon.com with our affiliate
> link: http://amzn.to/2dEadiD