Thanks for the response, your solution was helpful.
The date is a minor component to a barcode for a manufacturing process.
I get the date from the production line as mm-dd-yyyy on the build. I
have to create a barcode with as few intergers as possible. Completed,
David Portas wrote:
> 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
> --
