Concatenating Numbers/Converting Dates to Integers

  • Thread starter Thread starter grrr223
  • Start date Start date
G

grrr223

My ERP software stores all dates as integers. So originally, I wrote
T-SQL function to convert these integer dates to normal people dates i
the query I use as the recordset for my report. Well...that worked fin
on 1,000 rows, but NOT for 100,000. So I've figured out that if
convert my normal person date parameter to an integer date, then SQ
only has to convert my 1 parameter instead of having to convert 100,00
fields, (actually, 300,000 because I have 3 date columns).

So my question is, what is the best way to do this? This is what I hav
so far:



SET @Macola = Cast(Datepart(yy,@MacolaDate) as varchar)
Cast(Datepart(mm,@MacolaDate) as varchar)
Cast(Datepart(dd,@MacolaDate) as varchar)



However, I want the leading zeros for the month and day. For example i
I enter '1/1/2004' into this function, it returns 200411, but I need i
to return 20040101.

Any suggestions would be greatly apprectiated. Thank you
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Does ERP s/w store the data in an MS SQL Server db? SQL'r stores the
dates as numbers (floating point). From your example, it seems your
data is being accessed using T-SQL, which implies the data is in
SQL'r. If you want to display the date value in "human readable"
format do something like this:

When @MacolaDate has the date value for Feb 25, 2004.

DECLARE @Macola VARCHAR(12)

SET @Macola = CAST(@MacolaDate As VARCHAR)

@Macola now equals "Feb 25, 2004"

or

SET @Macola = CONVERT(VARCHAR, @MacolaDate, 101)

@Macola now equals "2/25/2004"

See the SQL Books on line article "Cast and Convert."


MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQD1F+4echKqOuFEgEQJ0+ACZAQ3Z5YPCqy09EgCGgly6ZchX3V8AnjIc
YoocNAyidAhfTLT9ywxpc/0V
=lCgd
-----END PGP SIGNATURE-----
 
Back
Top