Date Datatype Oddity - Ref "Like"

While trying to resolve a very endepth data issue against
healthcare claims data (8 years = rows), have discovered
an oddity.
When I run a "LIKE '% %'" (two spaces) against a datetime
or smalldatetime data type field, all dates with days less
than 10 return.
Could someone explain, this resultset, since datetime is
actually stored has an equivalant to two 4 bit integers
(one for date and one for time)?
This occurs against SQL 2000 (6.5 compatability DB, 7.0
compatability DB, 2000 DB) and also against SQL 2005 Beta
2 (2005 DB).
John Whitehead
MSSQLDBA (9years)Sorry (8 years = rows) should read (8 years = 31 million
rows)
JohnW
>--Original Message--
>While trying to resolve a very endepth data issue against
>healthcare claims data (8 years = rows), have discovered
>an oddity.
>When I run a "LIKE '% %'" (two spaces) against a
datetime
>or smalldatetime data type field, all dates with days
less
>than 10 return.
>Could someone explain, this resultset, since datetime is
>actually stored has an equivalant to two 4 bit integers
>(one for date and one for time)?
>This occurs against SQL 2000 (6.5 compatability DB, 7.0
>compatability DB, 2000 DB) and also against SQL 2005 Beta
>2 (2005 DB).
>
>John Whitehead
>MSSQLDBA (9years)
>.
>|||John,
I think this will explain it:
select convert(varchar, convert(datetime, '20040930'))
--Sep 30 2004 12:00AM
select convert(varchar, convert(datetime, '20041001'))
--Oct 1 2004 12:00AM
Notice that the 1 for Oct 1 is left-padded with an extra space.
"John Whitehead" <jwhitehead@.thearksolutions.com> wrote in message
news:24d801c4a7df$bc939270$a401280a@.phx.gbl...
> When I run a "LIKE '% %'" (two spaces) against a datetime
> or smalldatetime data type field, all dates with days less
> than 10 return.
> Could someone explain, this resultset, since datetime is
> actually stored has an equivalant to two 4 bit integers
> (one for date and one for time)?|||That's a very risky way to query datetime types. The default format varies
with your regional settings. Why are you querying like this? What are your
search criteria?
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"John Whitehead" <jwhitehead@.thearksolutions.com> wrote in message
news:24d801c4a7df$bc939270$a401280a@.phx.gbl...
While trying to resolve a very endepth data issue against
healthcare claims data (8 years = rows), have discovered
an oddity.
When I run a "LIKE '% %'" (two spaces) against a datetime
or smalldatetime data type field, all dates with days less
than 10 return.
Could someone explain, this resultset, since datetime is
actually stored has an equivalant to two 4 bit integers
(one for date and one for time)?
This occurs against SQL 2000 (6.5 compatability DB, 7.0
compatability DB, 2000 DB) and also against SQL 2005 Beta
2 (2005 DB).
John Whitehead
MSSQLDBA (9years)|||You should never be using LIKE against a [SMALL]DATETIME column. However,
it is easy to explain why you are getting this error. Run this:
PRINT GETDATE()
It doesn't print the actual numeric values that are stored, it converts it
to a string first. The appearance of this string will vary depending on a
few things (locale, date format, language, etc). But the same conversion
happens behind the scenes when you apply a string function such as LIKE.
BTW, I can't reproduce your "only days with less than 10 days" scenario. I
am betting that you are converting to an explicit format, or you haven't
disclosed your complete where clause. Can you provide a full repro script?
--
http://www.aspfaq.com/
(Reverse address to reply.)
"John Whitehead" <jwhitehead@.thearksolutions.com> wrote in message
news:24d801c4a7df$bc939270$a401280a@.phx.gbl...
> While trying to resolve a very endepth data issue against
> healthcare claims data (8 years = rows), have discovered
> an oddity.
> When I run a "LIKE '% %'" (two spaces) against a datetime
> or smalldatetime data type field, all dates with days less
> than 10 return.
> Could someone explain, this resultset, since datetime is
> actually stored has an equivalant to two 4 bit integers
> (one for date and one for time)?
> This occurs against SQL 2000 (6.5 compatability DB, 7.0
> compatability DB, 2000 DB) and also against SQL 2005 Beta
> 2 (2005 DB).
>
> John Whitehead
> MSSQLDBA (9years)|||Adam,
I would agree, however was not converting to a varchar
before the "like".
John
>--Original Message--
>John,
>I think this will explain it:
>select convert(varchar, convert(datetime, '20040930'))
>--Sep 30 2004 12:00AM
>
>select convert(varchar, convert(datetime, '20041001'))
>--Oct 1 2004 12:00AM
>
>Notice that the 1 for Oct 1 is left-padded with an extra
space.
>
>"John Whitehead" <jwhitehead@.thearksolutions.com> wrote
in message
>news:24d801c4a7df$bc939270$a401280a@.phx.gbl...
>> When I run a "LIKE '% %'" (two spaces) against a
datetime
>> or smalldatetime data type field, all dates with days
less
>> than 10 return.
>> Could someone explain, this resultset, since datetime is
>> actually stored has an equivalant to two 4 bit integers
>> (one for date and one for time)?
>
>.
>|||Tom,
You are absolutely right, and this is in no way a normal
resolution discovery path. We had been working on this
issue for a while, and this path was taken due to the fact
that we import this data from a "~" delimited, fixed width
file (that we have no control over) and was looking at the
possibility of an special character somehow making it into
the database. Have found some very endepth information
about how the sqlserver engine handles data and was just
wondering if this oddity was caused by that process.
This is not an issue - just an oddity
Thanks
John
>--Original Message--
>That's a very risky way to query datetime types. The
default format varies
>with your regional settings. Why are you querying like
this? What are your
>search criteria?
>--
>Tom
>----
--
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com
>
>"John Whitehead" <jwhitehead@.thearksolutions.com> wrote
in message
>news:24d801c4a7df$bc939270$a401280a@.phx.gbl...
>While trying to resolve a very endepth data issue against
>healthcare claims data (8 years = rows), have discovered
>an oddity.
>When I run a "LIKE '% %'" (two spaces) against a datetime
>or smalldatetime data type field, all dates with days less
>than 10 return.
>Could someone explain, this resultset, since datetime is
>actually stored has an equivalant to two 4 bit integers
>(one for date and one for time)?
>This occurs against SQL 2000 (6.5 compatability DB, 7.0
>compatability DB, 2000 DB) and also against SQL 2005 Beta
>2 (2005 DB).
>
>John Whitehead
>MSSQLDBA (9years)
>.
>|||The conversion happens implictly because LIKE expects only string arguments.
--
David Portas
SQL Server MVP
--|||> I would agree, however was not converting to a varchar
> before the "like".
You don't have to do it manually. LIKE is only relevant to string
arguments, and since [SMALL]DATETIME can be implicitly converted to VARCHAR,
that is what happens for you.|||You may want to consider DTS to look for the bad data as it imports it.
Failing that, you could import the field from the flat file into a
char/varchar column and then search for anomalies via SQL against the
column. We'd have to know a bit more of what you are seeking.
Tom
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"John Whitehead" <jwhitehead@.thearksolutions.com> wrote in message
news:254901c4a7e6$730376a0$a401280a@.phx.gbl...
Tom,
You are absolutely right, and this is in no way a normal
resolution discovery path. We had been working on this
issue for a while, and this path was taken due to the fact
that we import this data from a "~" delimited, fixed width
file (that we have no control over) and was looking at the
possibility of an special character somehow making it into
the database. Have found some very endepth information
about how the sqlserver engine handles data and was just
wondering if this oddity was caused by that process.
This is not an issue - just an oddity
Thanks
John
>--Original Message--
>That's a very risky way to query datetime types. The
default format varies
>with your regional settings. Why are you querying like
this? What are your
>search criteria?
>--
>Tom
>----
--
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com
>
>"John Whitehead" <jwhitehead@.thearksolutions.com> wrote
in message
>news:24d801c4a7df$bc939270$a401280a@.phx.gbl...
>While trying to resolve a very endepth data issue against
>healthcare claims data (8 years = rows), have discovered
>an oddity.
>When I run a "LIKE '% %'" (two spaces) against a datetime
>or smalldatetime data type field, all dates with days less
>than 10 return.
>Could someone explain, this resultset, since datetime is
>actually stored has an equivalant to two 4 bit integers
>(one for date and one for time)?
>This occurs against SQL 2000 (6.5 compatability DB, 7.0
>compatability DB, 2000 DB) and also against SQL 2005 Beta
>2 (2005 DB).
>
>John Whitehead
>MSSQLDBA (9years)
>.
>|||FINAL POST!!
This is the reason that I don't post to these boards!
THIS IS NOT AN ISSUE TO BE SOVLED - JUST AN ODDITY TO BE
EXPLAINED - "Confidentiality agreements prevent from
further explanations and process logic discussion"
Tom - I guarentee you that this is the first time in over
20 years of dealing with databases that I have run a LIKE
search against a date datatype. Some RDBMS's don't
allow "Like" against a date datatype.
Thanks Aaron, Logical, but is it a FACT, that the engine
would convert a numeric based data type to a character
base value in order to compare for the non-numeric "like"?
John
>--Original Message--
>You may want to consider DTS to look for the bad data as
it imports it.
>Failing that, you could import the field from the flat
file into a
>char/varchar column and then search for anomalies via SQL
against the
>column. We'd have to know a bit more of what you are
seeking.
> Tom
>--
>Tom
>----
--
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com
>
>"John Whitehead" <jwhitehead@.thearksolutions.com> wrote
in message
>news:254901c4a7e6$730376a0$a401280a@.phx.gbl...
>Tom,
>You are absolutely right, and this is in no way a normal
>resolution discovery path. We had been working on this
>issue for a while, and this path was taken due to the fact
>that we import this data from a "~" delimited, fixed width
>file (that we have no control over) and was looking at the
>possibility of an special character somehow making it into
>the database. Have found some very endepth information
>about how the sqlserver engine handles data and was just
>wondering if this oddity was caused by that process.
>This is not an issue - just an oddity
>Thanks
>John
>>--Original Message--
>>That's a very risky way to query datetime types. The
>default format varies
>>with your regional settings. Why are you querying like
>this? What are your
>>search criteria?
>>--
>>Tom
>>----
-
>--
>>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>>SQL Server MVP
>>Columnist, SQL Server Professional
>>Toronto, ON Canada
>>www.pinnaclepublishing.com
>>
>>"John Whitehead" <jwhitehead@.thearksolutions.com> wrote
>in message
>>news:24d801c4a7df$bc939270$a401280a@.phx.gbl...
>>While trying to resolve a very endepth data issue against
>>healthcare claims data (8 years = rows), have discovered
>>an oddity.
>>When I run a "LIKE '% %'" (two spaces) against a
datetime
>>or smalldatetime data type field, all dates with days
less
>>than 10 return.
>>Could someone explain, this resultset, since datetime is
>>actually stored has an equivalant to two 4 bit integers
>>(one for date and one for time)?
>>This occurs against SQL 2000 (6.5 compatability DB, 7.0
>>compatability DB, 2000 DB) and also against SQL 2005 Beta
>>2 (2005 DB).
>>
>>John Whitehead
>>MSSQLDBA (9years)
>>.
>.
>|||"John Whitehead" <jwhitehead@.thearksolutions.com> wrote in message
news:25f401c4a7ee$c91e7cd0$a401280a@.phx.gbl...
> This is the reason that I don't post to these boards!
It's free support... Sometimes you get what you pay for, other times
you get quite a bit more :-)
> Thanks Aaron, Logical, but is it a FACT, that the engine
> would convert a numeric based data type to a character
> base value in order to compare for the non-numeric "like"?
Yes, that's the only way the LIKE operator can work.|||> This is the reason that I don't post to these boards!
> THIS IS NOT AN ISSUE TO BE SOVLED - JUST AN ODDITY TO BE
> EXPLAINED
You got that, and more. Don't look a gift horse in the mouth.
> Thanks Aaron, Logical, but is it a FACT, that the engine
> would convert a numeric based data type to a character
> base value in order to compare for the non-numeric "like"?
Nah, I made it all up. *shrug*|||> BTW, I can't reproduce your "only days with less than 10 days" scenario.
> I
> am betting that you are converting to an explicit format, or you haven't
> disclosed your complete where clause. Can you provide a full repro
> script?
Aaron :
I'm not related to the original poster, I was just lurking, but here it is :
Create Table TableX
(ColX SmallDateTime Not Null )
Insert Into TableX Values ('20041101')
Insert Into TableX Values ('20041102')
Insert Into TableX Values ('20041109')
Insert Into TableX Values ('20041110')
Insert Into TableX Values ('20041111')
Insert Into TableX Values ('20041001')
Insert Into TableX Values ('20041002')
Insert Into TableX Values ('20041009')
Insert Into TableX Values ('20041010')
Insert Into TableX Values ('20041011')
Select * From TableX Where ColX Like '% %'
-- or Select * From TableX Where Cast(ColX as VarChar(20)) Like '% %'
Output in SQL Analyzer :
2004-11-01 00:00:00
2004-11-02 00:00:00
2004-11-09 00:00:00
2004-10-01 00:00:00
2004-10-02 00:00:00
2004-10-09 00:00:00
I have yyyy-mm-dd in my regional settings.
Regards,|||Sorry, I missed the two spaces in the original post, I thought there was
only one.
Regardless, the rest of the thread shows why this is a bad idea in the first
place.
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Craig Kenisston" <craigkenisston@.hotmail.com> wrote in message
news:#znUhsAqEHA.1164@.TK2MSFTNGP10.phx.gbl...
> > BTW, I can't reproduce your "only days with less than 10 days" scenario.
> > I
> > am betting that you are converting to an explicit format, or you haven't
> > disclosed your complete where clause. Can you provide a full repro
> > script?
> Aaron :
> I'm not related to the original poster, I was just lurking, but here it is
:
> Create Table TableX
> (ColX SmallDateTime Not Null )
> Insert Into TableX Values ('20041101')
> Insert Into TableX Values ('20041102')
> Insert Into TableX Values ('20041109')
> Insert Into TableX Values ('20041110')
> Insert Into TableX Values ('20041111')
> Insert Into TableX Values ('20041001')
> Insert Into TableX Values ('20041002')
> Insert Into TableX Values ('20041009')
> Insert Into TableX Values ('20041010')
> Insert Into TableX Values ('20041011')
> Select * From TableX Where ColX Like '% %'
> -- or Select * From TableX Where Cast(ColX as VarChar(20)) Like '% %'
> Output in SQL Analyzer :
> 2004-11-01 00:00:00
> 2004-11-02 00:00:00
> 2004-11-09 00:00:00
> 2004-10-01 00:00:00
> 2004-10-02 00:00:00
> 2004-10-09 00:00:00
> I have yyyy-mm-dd in my regional settings.
> Regards,
>