Re: Complex Query
- From: "David Portas" <REMOVE_BEFORE_REPLYING_dportas@xxxxxxx>
- Date: 31 May 2006 12:56:59 -0700
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
--
.
- Follow-Ups:
- Re: Complex Query
- From: Uleric
- Re: Complex Query
- References:
- Complex Query
- From: Uleric
- Complex Query
- Prev by Date: Complex Query
- Next by Date: Re: Complex Query
- Previous by thread: Complex Query
- Next by thread: Re: Complex Query
- Index(es):
Relevant Pages
|
