Remove decimals from a nondeterministic field

  • here is what I have:

    select 'D', '001', '1714827671', b.VCHRNMBR, b.DOCAMNT,

    b.DOCDATE, b.DOCNUMBR, d.VNDCHKNM, b.VOIDED

    from CM00100 as a

    inner join PM30200 as b

    on a.CHEKBKID = b.CHEKBKID

    inner join PM00200 as d

    on b.VENDORID = d.VENDORID

    and a.CHEKBKID = 'OP FTB'

    and b.DOCDATE = convert(varchar(8), getdate(), 112)

    This is a 2 part question -

    1st - the field b.DOCAMNT returns with decimals (164.23 example - and usually there are 50 - 100 results returned for different check amounts) and the bank requires that there be no decimal in the file.

    2nd - the b.DOCDATE field needs to reflect as YYYYMMDD, and the conversion is not working .... it still returns 2009-04-14 00:00:00:000 Please help !! THANKS IN ADVANCE ! :hehe:

  • This is a 2 part question -

    1st - the field b.DOCAMNT returns with decimals (164.23 example - and usually there are 50 - 100 results returned for different check amounts) and the bank requires that there be no decimal in the file.

    2nd - the b.DOCDATE field needs to reflect as YYYYMMDD, and the conversion is not working .... it still returns 2009-04-14 00:00:00:000

    1 - hhmmm how could i use math to remove the decimal place?

    2 - The answer is in your where clause.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • On the first item do you need the decimal data still there? For example if the field contains data like 123.45 do you need 123 or 12345? If you need 12345 how will they know its a two decimal position value or is that always assumed? A little more information about how you need to format it and rules around it might help someone give an exact answer. If you need just 123 you could use a function like FLOOR() or convert it to an int type data type. IF you need the full amount then you may need to convert to string and replace the "." with an empty string. You can also multiply it out into a whol number if its a given 2 decimal always.

    On the second item, you need to do the formating of the date in the SELECT portion of you statement and not the WHERE clause. The where clause only determines criteria of what result set is returned, and not the formatting of the returned data. Do a convert with a date style flag in the select portion of your statement on that field.

  • Thank you for your fast reply.

    If the field has 123.45, when the decimal is removed, it needs to be 12345. I am trying to convert this data all to a text file to upload to the bank. :w00t:

  • PS - Thought this might help as well - extracting from data tables and inserting data in to a temp table, so that the field size matches the bank requirements.

    CREATE TABLE dbo.AP_POSPAY

    (

    ID char(1) NOT NULL,

    [Bank Number] char(3) NOT NULL,

    Account char(10) NOT NULL,

    [Ck Number] char(10) NOT NULL,

    Amount char (13) NOT NULL,

    Issued char (8) NOT NULL,

    [Additional Data] char(30) NOT NULL,

    Payee char(80) NOT NULL,

    Void char(1) NOT NULL

    ) ON [PRIMARY]

  • if it is ALWAYS 2 decimals just multiply by 100

    otherwise cast it to a varchar and then replace '.' with ''

    😛

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Hello I took your code and made the following changes to it

    --Create Temp table to load converted data

    Create Table #Temp

    (

    col1 char(1),

    Trans char(3),

    Actnumb char(10),

    Vchnmbr char(20),

    Docamnt int,

    Docdate char(8),

    Docnumb char(20),

    Vndchknm char(64),

    Voided int

    )

    INSERT #Temp

    select 'D', '001', '1714827671', b.VCHRNMBR, (b.DOCAMNT * 100), --Remove the Decimal Points

    --Convert the date to YYYYDDMM format

    CONVERT(VARCHAR(8), CONVERT(VARCHAR(4), YEAR(b.DOCDATE))+ RIGHT( CONVERT(VARCHAR(2), DATEPART(dd, b.DOCDATE)),3) + RIGHT('0' + CONVERT(VARCHAR(2), DATEPART(mm, b.DOCDATE)), 2)) as Docdate,

    b.DOCNUMBR, d.VNDCHKNM, b.VOIDED, b.DOCDATE

    from CM00100 as a

    inner join PM30200 as b

    on a.CHEKBKID = b.CHEKBKID

    inner join PM00200 as d

    on b.VENDORID = d.VENDORID

    and a.CHEKBKID = 'USD_RBC_PAY'

    Select * from #Temp

    Drop Table #Temp

  • don.craig (4/15/2009)


    Hello I took your code and made the following changes to it

    --Create Temp table to load converted data

    Create Table #Temp

    (

    col1 char(1),

    Trans char(3),

    Actnumb char(10),

    Vchnmbr char(20),

    Docamnt int,

    Docdate char(8),

    Docnumb char(20),

    Vndchknm char(64),

    Voided int

    )

    INSERT #Temp

    select 'D', '001', '1714827671', b.VCHRNMBR, (b.DOCAMNT * 100), --Remove the Decimal Points

    --Convert the date to YYYYDDMM format

    CONVERT(VARCHAR(8), CONVERT(VARCHAR(4), YEAR(b.DOCDATE))+ RIGHT( CONVERT(VARCHAR(2), DATEPART(dd, b.DOCDATE)),3) + RIGHT('0' + CONVERT(VARCHAR(2), DATEPART(mm, b.DOCDATE)), 2)) as Docdate,

    b.DOCNUMBR, d.VNDCHKNM, b.VOIDED, b.DOCDATE

    from CM00100 as a

    inner join PM30200 as b

    on a.CHEKBKID = b.CHEKBKID

    inner join PM00200 as d

    on b.VENDORID = d.VENDORID

    and a.CHEKBKID = 'USD_RBC_PAY'

    Select * from #Temp

    Drop Table #Temp

    Hi Don

    Nice solution! Just one little suggestion, since the date format YYYYMMDD is the standard ISO date format I would use CONVERT:

    SELECT CONVERT(CHAR(8), GETDATE(), 112)

    Greets

    Flo

  • slange (4/15/2009)


    if it is ALWAYS 2 decimals just multiply by 100

    otherwise cast it to a varchar and then replace '.' with ''

    😛

    ... and if you have to remove other decimals after the first both try FLOOR:

    SELECT FLOOR(234.740423 * 100)

    Greets

    Flo

  • Hi Flo,

    Thank you for your suggestions.

    Don

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply