473,320 Members | 1,865 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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?

May 31 '06 #1
3 1353
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: Starbuck | last post by:
OK, first let me say that I am no DB person. But I have a user here who keeps getting this error whenever she does, whatever it is she does, with databases... A google search takes me to...
2
by: Mikel | last post by:
I am trying to get around the problem "The expression you have entered is too complex" for a select query. (The example below is not the expression that is giving me headaches.) So I am thinking...
4
by: ED | last post by:
I am attempting to to write a query that has a numerous nested IIf statements. The problem that I am having is that it is to long of a query to be built in design mode and when I build it in sql...
8
by: Matt | last post by:
Hi all, Thank you for taking the time. I have a database with 45 tables on it. 44 tables are linked to a main table through a one to one relationship. My question is, is there no way i can...
2
by: Ben de Vette | last post by:
Hi, I'm using the querybuilder when updating a record in a table (Access). However, I get a "Query is too complex" message. The Primary key is autonumbered. Why is it making such a complex...
1
by: arun | last post by:
Query is too complex -------------------------------------------------------------------------------- Hi, I was trying to solve this problem since last two days but couldn't find any solution. ...
1
by: Randy Volkart | last post by:
I'm trying to fix a glitch in a complex access database, and have a fairly complex problem... unless there's some obscure easy fix I don't know being fairly new with Access. Basically, the area...
19
by: kawaks40 | last post by:
Hi everyone :) I just recently started using access/sql. and right away I ran into this problem "SQL expression too complex" I google'd a lot on what it means, and the only workaround I've...
3
by: Eric Davidson | last post by:
DB2 9.5 I keep geting the message. SQL0101N The statement is too long or too complex. SQLSTATE=54001 When one of my sql statements takes over 60 seconds to compile the sql statement. Is...
0
crystal2005
by: crystal2005 | last post by:
Hi, I am having trouble with some complex SQL queries. I’ve got winestore database, taken from Web Database Application with PHP and MySQL book. And some question about queries as the following ...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.