Conversion of decimal and date/datetime types into custom varchar equivalents
I am开发者_JAVA百科 generating a flat text file via an SSIS package. The package reads data from a table, creates a fixed length text file and ftp's the file to a partner.
The data arrives into my table via a process that I have control over so I can make adjustments based on suggestions.
My quandry is this.
I have a two fields, lets call them: DateOfHire - was going to store as a datetime or a date, although time is irrelevant Earnings - this will always have a scale of 6 with a 2 precision i.e. decimal (6,2)
Now my question is this, since the ultimate destination of these two fields is going to be a text file, and I have to change their format before write, should I store them as what they should be stored as, i.e. a date as a date and a decimal value as a decimal or should I just make the table store varchars?
The reason I ask is this: If the date of hire is 10/07/10 -> the record in the file will be written as 100710 If the Earnings are $250.99 -> the record in the file will be written as 025099
That is the first question.
The second question is how do I convert a decimal like 250.99 into 025099 given that 9999.99 is the absolute possible maximum.
SQL Server version: 2008
Question 1:
Store appropriately (date as datetime etc). Other clients may use this data. And keep it human readable.
Convert in the client (whether for render in a GUI or for a file export) For a GUI, you may need regional settings. For a file, I would consider doing it in SQL though as part of the bcp/read.
Question 2:
Don't convert as a numeric, but manipulate as a string given how simple it is. And you avoid datetype issues
For a value:
SELECT RIGHT('000000' + LTRIM(REPLACE(STR ('250.99', 7, 2), '.', '')), 6)
Or based on your switch example
CASE
WHEN Earnings = 250.99 THEN
--varchar, not decimal
RIGHT('000000' + LTRIM(REPLACE(STR ('250.99', 7, 2), '.', '')), 6)
ELSE CONVERT(varchar(6), DateOfHire, 12) --varchar. not datetime
END AS Whatever
First, I don't recomend you to store these values in varchar field.
When you exports the values to the text file you can use a query to prepare the data.
For example,
select convert(varchar(8), DateOfHire, 112) DateOfHireText,
,replicate('0', 6- len(convert(varchar(10), CEILING(earnings * 100)))) + convert(varchar(10), CEILING(earnings * 100)) EarningsText
from mytable
This return the data in the format you want.
精彩评论