April 15, 2009 at 9:15 am
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:
April 15, 2009 at 10:18 am
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/
April 15, 2009 at 10:19 am
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.
April 15, 2009 at 10:35 am
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:
April 15, 2009 at 10:40 am
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]
April 15, 2009 at 10:43 am
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/
April 15, 2009 at 11:36 am
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
April 15, 2009 at 2:28 pm
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
April 15, 2009 at 2:31 pm
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)
Greets
Flo
April 16, 2009 at 5:36 am
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