|
|
Posted by Uleric on 05/31/06 20:27
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/ms130214(en-US,SQL.90).aspx
> --
Navigation:
[Reply to this message]
|