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?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/...US,SQL.90).aspx
--|||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,
it looks like:

Inputs: product_run+man_plant+product_serial+man_date

Completed: 0005232124109230053106 (000523+212+4109230+053106)

That gets converted to a barcode.

There is no likelihood for the barcode to be duplicated past y2100
since there will be no product run cycle from the past in future dates.

The real date is of course stored in the manufacture database as an ISO
date/time.

Again, thank you.

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/...US,SQL.90).aspx
> --|||Uleric (Uleric@.gmail.com) writes:
> 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.

Why not

declare @.date datetime
select @.date = '2006-02-02'
select substring(datename(year, @.date), 3, 4) +
substring(convert(char(4), datepart(dayofyear, @.date) + 1000), 2, 3)

That's one digit less.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx