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)
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[vbcol=seagreen]
>news:24d801c4a7df$bc939270$a401280a@.phx.gbl...
datetime[vbcol=seagreen]
less
>
>.
>
|||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[vbcol=seagreen]
>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
>default format varies
>this? What are your
-[vbcol=seagreen]
>--
>in message
datetime[vbcol=seagreen]
less
>.
>