Conversion of decimal and date/datetime types into custom varchar equivalents

Question

I am 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

Answer 1

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

Answer 2

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.