By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,806 Members | 1,829 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,806 IT Pros & Developers. It's quick & easy.

Complex Query

P: n/a
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?

May 31 '06 #1
Share this Question
Share on Google+
3 Replies


P: n/a
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/m...S,SQL.90).aspx
--

May 31 '06 #2

P: n/a
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/m...S,SQL.90).aspx
--


May 31 '06 #3

P: n/a
Uleric (Ul****@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
May 31 '06 #4

This discussion thread is closed

Replies have been disabled for this discussion.