Complex Query

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?

.

Relevant Pages

  • Re: Complex Query
    ... Everything works except chomping the year to the last 2 digits. ... possible use standard and well-defined formats like the ISO formats. ... State what version of SQL Server you are using and specify the content ...
    (comp.databases.ms-sqlserver)
  • Re: SUBSTR in query
    ... I just want to add a SUBSTR to capture the first 5 digits of a field. ... FROM tblMember ...
    (microsoft.public.access.queries)