Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Remove decimals from a nondeterministic field Rate Topic Display Mode Topic Options
Author
 Message
 Posted Wednesday, April 15, 2009 9:15 AM
 Grasshopper Group: General Forum Members Last Login: Tuesday, September 6, 2011 12:48 PM Points: 11, Visits: 41
 here is what I have: select 'D', '001', '1714827671', b.VCHRNMBR, b.DOCAMNT, b.DOCDATE, b.DOCNUMBR, d.VNDCHKNM, b.VOIDEDfrom CM00100 as ainner join PM30200 as b on a.CHEKBKID = b.CHEKBKIDinner join PM00200 as d on b.VENDORID = d.VENDORIDand 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 !
Post #697627
 Posted Wednesday, April 15, 2009 10:18 AM
 SSCoach Group: General Forum Members Last Login: Yesterday @ 2:44 PM Points: 16,134, Visits: 16,835
 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:0001 - 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 Moden's 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)
Post #697705
 Posted Wednesday, April 15, 2009 10:19 AM
 Old Hand Group: General Forum Members Last Login: Thursday, May 19, 2016 10:24 AM Points: 383, 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.
Post #697706
 Posted Wednesday, April 15, 2009 10:35 AM
 Grasshopper Group: General Forum Members Last Login: Tuesday, September 6, 2011 12:48 PM 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.
Post #697718
 Posted Wednesday, April 15, 2009 10:40 AM
 Grasshopper Group: General Forum Members Last Login: Tuesday, September 6, 2011 12:48 PM 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]
Post #697726
 Posted Wednesday, April 15, 2009 10:43 AM
 SSCoach Group: General Forum Members Last Login: Yesterday @ 2:44 PM Points: 16,134, Visits: 16,835
 if it is ALWAYS 2 decimals just multiply by 100otherwise 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 Moden's 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)
Post #697732
 Posted Wednesday, April 15, 2009 11:36 AM
 SSC Journeyman Group: General Forum Members Last Login: Thursday, July 3, 2014 6:50 AM Points: 75, Visits: 79
 Hello I took your code and made the following changes to it--Create Temp table to load converted dataCreate 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.DOCDATEfrom 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
Post #697772
 Posted Wednesday, April 15, 2009 2:28 PM
 SSCommitted Group: General Forum Members Last Login: Thursday, February 5, 2015 1:08 AM Points: 1,893, Visits: 3,934
 don.craig (4/15/2009)Hello I took your code and made the following changes to it--Create Temp table to load converted dataCreate 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.DOCDATEfrom 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 #TempHi DonNice 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)`GreetsFlo The more I learn, the more I know what I do not knowBlog: Things about Software Architecture, .NET development and T-SQLHow to Post Data/Code to get the best Help How to Post Performance Problems
Post #697916
 Posted Wednesday, April 15, 2009 2:31 PM
 SSCommitted Group: General Forum Members Last Login: Thursday, February 5, 2015 1:08 AM Points: 1,893, Visits: 3,934
 slange (4/15/2009)if it is ALWAYS 2 decimals just multiply by 100otherwise 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)`GreetsFlo The more I learn, the more I know what I do not knowBlog: Things about Software Architecture, .NET development and T-SQLHow to Post Data/Code to get the best Help How to Post Performance Problems
Post #697917
 Posted Thursday, April 16, 2009 5:36 AM
 SSC Journeyman Group: General Forum Members Last Login: Thursday, July 3, 2014 6:50 AM Points: 75, Visits: 79
 Hi Flo,Thank you for your suggestions.Don
Post #698307

 Permissions