|
|
Posted by David Portas on 05/31/06 19:56
Uleric wrote:
> I am converting a date to a variable, then stripping out unwanted
> hyphens, making sure there is a 0 in the month/day, and chomping the
> year to the last 2 digits. The end result is a 6 digit integer that I
> can use in my SQL.
>
> 05-31-2006 changes to 053106
>
> Everything works except chomping the year to the last 2 digits. Substr
> would normally work, but my results are ommiting the substring
> completely.
>
> BEGIN
> RIGHT('00' + CAST(DATEPART(mm, @date) as varchar(2)), 2 ) +
> RIGHT('00' + CAST(DATEPART(dd, @date) as varchar(2)), 2 ) +
> SUBSTRING(CAST(DATEPART(yyyy, @date) as varchar(2)), -2, 2)
>
> Any clues? Corrections?
SELECT REPLACE(CONVERT(CHAR(8),@date,1),'/','') AS dt;
dt
----------
053106
That's a very poor format in which to store or manipulate a date. It's
ambiguous, hard to read, won't sort chronologically and it loses the
century. You may remember a few years ago the world spent billions of
dollars correcting errors like this one.
My advice is that you store dates as dates and where that isn't
possible use standard and well-defined formats like the ISO formats.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--
Navigation:
[Reply to this message]
|