Formating a select statement...

RinoDM · March 18th, 2009, 02:08 PM

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

Old Pedant · March 19th, 2009, 12:19 AM

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
       ...

urtrivedi · March 19th, 2009, 12:38 AM

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.

Jeff Moden · March 19th, 2009, 01:11 AM

Quote:

Originally Posted by RinoDM

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.

RinoDM · March 19th, 2009, 08:52 AM

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

RinoDM · March 19th, 2009, 09:09 AM

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

Jeff Moden · March 19th, 2009, 09:42 PM

Quote:

Originally Posted by RinoDM

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.

Old Pedant · March 22nd, 2009, 04:42 PM

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.

RinoDM · March 24th, 2009, 11:53 AM

it worked thanks