Wrox Programmer Forums
Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2000 > SQL Server 2000
| Search | Today's Posts | Mark Forums Read
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old March 18th, 2009, 02:08 PM
Friend of Wrox
Points: 550, Level: 8
Points: 550, Level: 8 Points: 550, Level: 8 Points: 550, Level: 8
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2006
Location: , , .
Posts: 112
Thanks: 12
Thanked 0 Times in 0 Posts
Default Formating a select statement...

I would like to format my output ex.: Select Date,Store,SKU,Flag,Qty,Price from Details where Date >= '03/17/09' output is : 2009-03-17 00:00:00 5 921711 1 1 10.9600 i need an out like... 20090317 005 0000000921711 01 00001 0001096 i got the date to work for now... Select CONVERT(char(8), TransactionDetailDate, 112),.... output is 20090317 can i do the same for the other fields...thanks rino
  #2 (permalink)  
Old March 19th, 2009, 12:19 AM
Friend of Wrox
 
Join Date: Jun 2008
Location: Snohomish, WA, USA
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
Default

Code:
SELECT CONVERT(char(8),TransactionDetailDate, 112 ) AS date,
       RIGHT( '000' + CONVERT(varchar,store), 3 ) AS store,
       RIGHT( '0000000000000' + CONVERT(varchar,SKU), 13) AS sku,
       ... etc. ...,
       RIGHT( '0000000' + CONVERT(varchar, ROUND(100 * price,2)), 7) AS price
       ...
The Following User Says Thank You to Old Pedant For This Useful Post:
RinoDM (March 19th, 2009)
  #3 (permalink)  
Old March 19th, 2009, 12:38 AM
Friend of Wrox
 
Join Date: Oct 2007
Location: , , .
Posts: 130
Thanks: 0
Thanked 3 Times in 3 Posts
Send a message via AIM to urtrivedi
Default

Select CONVERT(char(8),TransactionDetailDate, 112 ) AS date, Store,SKU,Flag,Qty,Price replicate('0',3-len(cast(Store as varchar(25))))+cast(Store as varchar(25)) as store,
replicate('0',13-len(cast(SKU as varchar(25))))+cast(SKUas varchar(25)) as sku,
replicate('0',2-len(cast(Flag as varchar(25))))+cast(Flagas varchar(25)) as flag,
replicate('0',5-len(cast(Qty as varchar(25))))+cast(Qtyas varchar(25)) as qty,
replicate('0',7-len(cast(Price as varchar(25))))+cast(Price as varchar(25)) as price,
from Details where Date >= '03/17/09'

I am not sure about price column. but you can vary length by changing 7-len to your choice in price column.
__________________
urt

Help yourself by helping someone.

Last edited by urtrivedi; March 19th, 2009 at 12:44 AM..
The Following User Says Thank You to urtrivedi For This Useful Post:
RinoDM (March 19th, 2009)
  #4 (permalink)  
Old March 19th, 2009, 01:11 AM
Friend of Wrox
Points: 1,536, Level: 15
Points: 1,536, Level: 15 Points: 1,536, Level: 15 Points: 1,536, Level: 15
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2006
Location: , MI, USA.
Posts: 475
Thanks: 0
Thanked 9 Times in 9 Posts
Default Unless it's for a file, do it in the GUI

Quote:
Originally Posted by RinoDM View Post
I would like to format my output
I'ts always a testy subject... why do you want to do such formatting on the server side? Why not distribute the extra clock cycles necessary to do such formatting either on the GUI side or on the report generator side? If it's for direct output to a file, then I can see doing such a thing. But, I can't justify doing it on the server side if a GUI or report generator are being used.
__________________
--Jeff Moden
  #5 (permalink)  
Old March 19th, 2009, 08:52 AM
Friend of Wrox
Points: 550, Level: 8
Points: 550, Level: 8 Points: 550, Level: 8 Points: 550, Level: 8
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2006
Location: , , .
Posts: 112
Thanks: 12
Thanked 0 Times in 0 Posts
Default

Thank you all for getting back to me...Looks like Old Pedant select worked just fine for what i needed...Thank you Old Pedant...For urtrivedi i didn't try it yet but i will...To answer Jeff the reason why i needed this, we are changing our POS system and the new system requires these formats for the import...and since with SQL when the results comes out i can save as file format to ansi and column delimiter to tab delimited exacty what is needed for import...Thanks againRino
  #6 (permalink)  
Old March 19th, 2009, 09:09 AM
Friend of Wrox
Points: 550, Level: 8
Points: 550, Level: 8 Points: 550, Level: 8 Points: 550, Level: 8
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2006
Location: , , .
Posts: 112
Thanks: 12
Thanked 0 Times in 0 Posts
Default

just saw something wrong with the Price column...1st the .00 is still coming out ex.: 2598.002nd if the price is negative it's coming out like this ex.: 0-99.00 or -267.00i tried doing this since my flag holds the -1 or +1 value i can multiple it with the price making it positive but it still didn't work......RIGHT( '0000000' + CONVERT(varchar, ROUND(100 * (Price*Flag),2)), 7)...Would you guys know why....Rino
  #7 (permalink)  
Old March 19th, 2009, 09:42 PM
Friend of Wrox
Points: 1,536, Level: 15
Points: 1,536, Level: 15 Points: 1,536, Level: 15 Points: 1,536, Level: 15
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2006
Location: , MI, USA.
Posts: 475
Thanks: 0
Thanked 9 Times in 9 Posts
Default

Quote:
Originally Posted by RinoDM View Post
To answer Jeff the reason why i needed this, we are changing our POS system and the new system requires these formats for the import...and since with SQL when the results comes out i can save as file format to ansi and column delimiter to tab delimited exacty what is needed for import...Thanks againRino
Heh... POS = PITA. Thanks for the feedback, Rino... I always like to know why people need to do things like this.
__________________
--Jeff Moden
  #8 (permalink)  
Old March 22nd, 2009, 04:42 PM
Friend of Wrox
 
Join Date: Jun 2008
Location: Snohomish, WA, USA
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
Default

What do you *WANT* the negative values to come back as???

-0000276
or
0000276-
or what???
And if you want a minus sign, do you want a + for positive values? Or an extra leading zero? Or leading space? Or or or???
Need you to specify *PRECISELY*.

As for the price still having .00 in it: Must be a currency field; I assumed a double field. So maybe:
Code:
RIGHT( '0000000' + CONVERT(varchar, CONVERT(INT, ROUND(100 * price,2)) ), 7) AS price
Think that should do it.
The Following User Says Thank You to Old Pedant For This Useful Post:
RinoDM (March 24th, 2009)
  #9 (permalink)  
Old March 24th, 2009, 11:53 AM
Friend of Wrox
Points: 550, Level: 8
Points: 550, Level: 8 Points: 550, Level: 8 Points: 550, Level: 8
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2006
Location: , , .
Posts: 112
Thanks: 12
Thanked 0 Times in 0 Posts
Default

it worked thanks


Similar Threads
Thread Thread Starter Forum Replies Last Post
select Statement gregalb SQL Server 2000 3 January 15th, 2008 12:00 AM
select statement help... RinoDM SQL Server 2000 13 January 10th, 2008 08:34 PM
Select from another select statement to a repeater simsen ASP.NET 2.0 Professional 0 May 2nd, 2007 04:34 PM
Select Statement jmss66 VB How-To 3 March 13th, 2006 03:55 PM
select statement collie MySQL 0 January 14th, 2004 04:36 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.