|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
decimal (11,2) to a fixed-formateto extract this column as a fixed formatted data type. How can I do this so that all my decimal places will be lined up like so... 218400.00 054600.00 264000.00 280000.00 070000.00 Rather than how it looks now... 218400.00 54600.00 264000.00 280000.00 70000.00 Any help would be greatly appreciated. Thanks! wnfisba T-SQL is not a good choice for doing formating. That should normally be
done be the front end. You can, however, do something like the following, just remember that you will then be returning a varchar, not a decimal. Select Right('000000000000' + Cast(MyColumn As varchar(12)), 12) As MyColumn From @a Order By MyColumn; Tom Show quote "wnfisba" <wnfi***@discussions.microsoft.com> wrote in message news:47AC646A-789D-4548-BA47-FA3E92AFA62A@microsoft.com... > We have a dollar amount column that is defined as decimal (11,2) and we > need > to extract this column as a fixed formatted data type. How can I do this > so > that all my decimal places will be lined up like so... > > 218400.00 > 054600.00 > 264000.00 > 280000.00 > 070000.00 > > Rather than how it looks now... > 218400.00 > 54600.00 > 264000.00 > 280000.00 > 70000.00 > > Any help would be greatly appreciated. > > Thanks! > > wnfisba Tom,
I am using a DTS with SQL to get the data out Tom. But I cannot seem to get to the precision and scale columns within the DTS. Do you have any suggestions how to do this internall;y with DTS??? Let me know Tom. Thanks! Show quote "Tom Cooper" wrote: > T-SQL is not a good choice for doing formating. That should normally be > done be the front end. You can, however, do something like the following, > just remember that you will then be returning a varchar, not a decimal. > > Select Right('000000000000' + Cast(MyColumn As varchar(12)), 12) As MyColumn > From @a > Order By MyColumn; > > Tom > > "wnfisba" <wnfi***@discussions.microsoft.com> wrote in message > news:47AC646A-789D-4548-BA47-FA3E92AFA62A@microsoft.com... > > We have a dollar amount column that is defined as decimal (11,2) and we > > need > > to extract this column as a fixed formatted data type. How can I do this > > so > > that all my decimal places will be lined up like so... > > > > 218400.00 > > 054600.00 > > 264000.00 > > 280000.00 > > 070000.00 > > > > Rather than how it looks now... > > 218400.00 > > 54600.00 > > 264000.00 > > 280000.00 > > 70000.00 > > > > Any help would be greatly appreciated. > > > > Thanks! > > > > wnfisba > > > DTS out to what sort of output? Another table, an EXCEL file, a flat file?
Tom Show quote "wnfisba" <wnfi***@discussions.microsoft.com> wrote in message news:64215DBD-DE09-493D-92E9-B604912CC328@microsoft.com... > Tom, > > I am using a DTS with SQL to get the data out Tom. But I cannot seem to > get > to the precision and scale columns within the DTS. > > Do you have any suggestions how to do this internall;y with DTS??? > > Let me know Tom. > > Thanks! > > "Tom Cooper" wrote: > >> T-SQL is not a good choice for doing formating. That should normally be >> done be the front end. You can, however, do something like the >> following, >> just remember that you will then be returning a varchar, not a decimal. >> >> Select Right('000000000000' + Cast(MyColumn As varchar(12)), 12) As >> MyColumn >> From @a >> Order By MyColumn; >> >> Tom >> >> "wnfisba" <wnfi***@discussions.microsoft.com> wrote in message >> news:47AC646A-789D-4548-BA47-FA3E92AFA62A@microsoft.com... >> > We have a dollar amount column that is defined as decimal (11,2) and we >> > need >> > to extract this column as a fixed formatted data type. How can I do >> > this >> > so >> > that all my decimal places will be lined up like so... >> > >> > 218400.00 >> > 054600.00 >> > 264000.00 >> > 280000.00 >> > 070000.00 >> > >> > Rather than how it looks now... >> > 218400.00 >> > 54600.00 >> > 264000.00 >> > 280000.00 >> > 70000.00 >> > >> > Any help would be greatly appreciated. >> > >> > Thanks! >> > >> > wnfisba >> >> >> DTS out to a flat file...I tried indicating the precision and scale within
the DTS but those fields were protected and I couldn't change them. Show quote "Tom Cooper" wrote: > DTS out to what sort of output? Another table, an EXCEL file, a flat file? > > Tom > > "wnfisba" <wnfi***@discussions.microsoft.com> wrote in message > news:64215DBD-DE09-493D-92E9-B604912CC328@microsoft.com... > > Tom, > > > > I am using a DTS with SQL to get the data out Tom. But I cannot seem to > > get > > to the precision and scale columns within the DTS. > > > > Do you have any suggestions how to do this internall;y with DTS??? > > > > Let me know Tom. > > > > Thanks! > > > > "Tom Cooper" wrote: > > > >> T-SQL is not a good choice for doing formating. That should normally be > >> done be the front end. You can, however, do something like the > >> following, > >> just remember that you will then be returning a varchar, not a decimal. > >> > >> Select Right('000000000000' + Cast(MyColumn As varchar(12)), 12) As > >> MyColumn > >> From @a > >> Order By MyColumn; > >> > >> Tom > >> > >> "wnfisba" <wnfi***@discussions.microsoft.com> wrote in message > >> news:47AC646A-789D-4548-BA47-FA3E92AFA62A@microsoft.com... > >> > We have a dollar amount column that is defined as decimal (11,2) and we > >> > need > >> > to extract this column as a fixed formatted data type. How can I do > >> > this > >> > so > >> > that all my decimal places will be lined up like so... > >> > > >> > 218400.00 > >> > 054600.00 > >> > 264000.00 > >> > 280000.00 > >> > 070000.00 > >> > > >> > Rather than how it looks now... > >> > 218400.00 > >> > 54600.00 > >> > 264000.00 > >> > 280000.00 > >> > 70000.00 > >> > > >> > Any help would be greatly appreciated. > >> > > >> > Thanks! > >> > > >> > wnfisba > >> > >> > >> > > > Then you do need to do the formatting with T-SQL. Just choose SQL Query
instead of Table/View in the Source tab of the Transform Data Task properties dialog. Then write the query to return the columns you want with the data formatted like you want it. Make sure all the columns in your result set have a name. I would normally write the query in QA and get it to do everything the way I wanted it, the cut and paste it into the box on the Transform Data Task properties dialog. For example, to get the CustomerID and ExtendedPrice with the price always taking 10 characters from the Invoices table in the Northwind database, you could use the query: Select CustomerID, Right('0000000000' + Cast(ExtendedPrice As varchar(10)), 10) As Price From Invoices Tom Show quote "wnfisba" <wnfi***@discussions.microsoft.com> wrote in message news:F8DAD5E0-905D-46CC-9862-8E51AFA33665@microsoft.com... > DTS out to a flat file...I tried indicating the precision and scale within > the DTS but those fields were protected and I couldn't change them. > > "Tom Cooper" wrote: > >> DTS out to what sort of output? Another table, an EXCEL file, a flat >> file? >> >> Tom >> >> "wnfisba" <wnfi***@discussions.microsoft.com> wrote in message >> news:64215DBD-DE09-493D-92E9-B604912CC328@microsoft.com... >> > Tom, >> > >> > I am using a DTS with SQL to get the data out Tom. But I cannot seem to >> > get >> > to the precision and scale columns within the DTS. >> > >> > Do you have any suggestions how to do this internall;y with DTS??? >> > >> > Let me know Tom. >> > >> > Thanks! >> > >> > "Tom Cooper" wrote: >> > >> >> T-SQL is not a good choice for doing formating. That should normally >> >> be >> >> done be the front end. You can, however, do something like the >> >> following, >> >> just remember that you will then be returning a varchar, not a >> >> decimal. >> >> >> >> Select Right('000000000000' + Cast(MyColumn As varchar(12)), 12) As >> >> MyColumn >> >> From @a >> >> Order By MyColumn; >> >> >> >> Tom >> >> >> >> "wnfisba" <wnfi***@discussions.microsoft.com> wrote in message >> >> news:47AC646A-789D-4548-BA47-FA3E92AFA62A@microsoft.com... >> >> > We have a dollar amount column that is defined as decimal (11,2) and >> >> > we >> >> > need >> >> > to extract this column as a fixed formatted data type. How can I do >> >> > this >> >> > so >> >> > that all my decimal places will be lined up like so... >> >> > >> >> > 218400.00 >> >> > 054600.00 >> >> > 264000.00 >> >> > 280000.00 >> >> > 070000.00 >> >> > >> >> > Rather than how it looks now... >> >> > 218400.00 >> >> > 54600.00 >> >> > 264000.00 >> >> > 280000.00 >> >> > 70000.00 >> >> > >> >> > Any help would be greatly appreciated. >> >> > >> >> > Thanks! >> >> > >> >> > wnfisba >> >> >> >> >> >> >> >> >> I found this to be a working method. Converting to a string and padding
that way. e.g. an example from pubs to pad a value: SELECT right(replicate('0',10) + convert(varchar(10),ytd_sales),10) FROM titles WHERE ytd_sales IS NOT NULL -- Andrew Show quote "wnfisba" <wnfi***@discussions.microsoft.com> wrote in message news:47AC646A-789D-4548-BA47-FA3E92AFA62A@microsoft.com... > We have a dollar amount column that is defined as decimal (11,2) and we > need > to extract this column as a fixed formatted data type. How can I do this > so > that all my decimal places will be lined up like so... > > 218400.00 > 054600.00 > 264000.00 > 280000.00 > 070000.00 > > Rather than how it looks now... > 218400.00 > 54600.00 > 264000.00 > 280000.00 > 70000.00 > > Any help would be greatly appreciated. > > Thanks! > > wnfisba  |
|||||||||||||||||||||||
Other interesting topics