SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Remove decimals from a nondeterministic field


Remove decimals from a nondeterministic field

Author
Message
SQL.Chick
SQL.Chick
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 41
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
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26412 Visits: 17557

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.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
dmc-608719
dmc-608719
Mr or Mrs. 500
Mr or Mrs. 500 (569 reputation)Mr or Mrs. 500 (569 reputation)Mr or Mrs. 500 (569 reputation)Mr or Mrs. 500 (569 reputation)Mr or Mrs. 500 (569 reputation)Mr or Mrs. 500 (569 reputation)Mr or Mrs. 500 (569 reputation)Mr or Mrs. 500 (569 reputation)

Group: General Forum Members
Points: 569 Visits: 787
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.
SQL.Chick
SQL.Chick
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 41
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
SQL.Chick
SQL.Chick
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 41
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]
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26412 Visits: 17557
if it is ALWAYS 2 decimals just multiply by 100

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

_______________________________________________________________

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.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
don.craig
don.craig
SSC Journeyman
SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)

Group: General Forum Members
Points: 75 Visits: 79
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
Florian Reischl
Florian Reischl
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3599 Visits: 3934
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


The more I learn, the more I know what I do not know
Blog: Things about Software Architecture, .NET development and T-SQL

How to Post Data/Code to get the best Help How to Post Performance Problems
Florian Reischl
Florian Reischl
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3599 Visits: 3934
slange (4/15/2009)
if it is ALWAYS 2 decimals just multiply by 100

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


... and if you have to remove other decimals after the first both try FLOOR:
SELECT FLOOR(234.740423 * 100)



Greets
Flo


The more I learn, the more I know what I do not know
Blog: Things about Software Architecture, .NET development and T-SQL

How to Post Data/Code to get the best Help How to Post Performance Problems
don.craig
don.craig
SSC Journeyman
SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)

Group: General Forum Members
Points: 75 Visits: 79
Hi Flo,

Thank you for your suggestions.

Don
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search