querying by date on smalldatetime

[Marc Pelletier]

Hello,

I have a table with a Day field, defined as smalldatetime. I am filling it
from a CSharp application with the following code:
DataRow r = dtStaDays.NewRo w();

r["Station_ID "]= station_ID;
r["Day"] = sd.Date;
r["Range"] = rangeTide;
etc.

However when I do a query using
"Select * FROM StationDays where Station_ID = 8 and Day = 06/01/2005"
I don't get any results even though that row exists. To make it work I have
to use
"Select * FROM StationDays where Station_ID = 8 and Day > 06/01/2005"
(which isn't very satisfactory
or
SELECT * FROM StationDays WHERE (Station_ID = 8) AND ([Day]
= CONVERT(DATETIM E, '2005-01-06 00:00:00', 102))

What can I do to simplify this query?

Thanks

Marc Pelletier

[Hugo Kornelis]

Re: querying by date on smalldatetime

On Fri, 07 Jan 2005 04:16:28 -0000, Marc Pelletier wrote:
[color=blue]
>I have a table with a Day field, defined as smalldatetime. I am filling it
>from a CSharp application with the following code:
> DataRow r = dtStaDays.NewRo w();
>
> r["Station_ID "]= station_ID;
> r["Day"] = sd.Date;
> r["Range"] = rangeTide;
>etc.
>
>However when I do a query using
>"Select * FROM StationDays where Station_ID = 8 and Day = 06/01/2005"
>I don't get any results even though that row exists. To make it work I have
>to use
>"Select * FROM StationDays where Station_ID = 8 and Day > 06/01/2005"
>(which isn't very satisfactory
>or
>SELECT * FROM StationDays WHERE (Station_ID = 8) AND ([Day]
>= CONVERT(DATETIM E, '2005-01-06 00:00:00', 102))
>
>What can I do to simplify this query?[/color]

Hi Marc,

The reason is that SQL Server has no datatype to store date only - the
datetime datatype stores both date and time. Your date constant 06/01/2005
(which is an ambiguous date format, by the way - I suggest you to only use
the unambiguous format YYYYMMDD, without seperators) will be converted to
a datetime value with the time portion equal to 00:00:00.000. However, the
values in the column Day are filled by your CSharp app (sd.Date) and
probably do hold a time portion. And of course, '20050106 13:40:57.530' is
not equal to '20050161 00:00:00.000'.

There are basically two ways to get around this. One os to change your
WHERE clauses. Not the way you have them above (the first version will
also return rows with a date after 06/01/2005 and the second will not be
able to use any index on the Day column). You'll have to change your logic
from "I want all rows with Day equal to 06/01/2005" to "I want all rows
with Day+Time from midnight 06/01/2005 up to but not including midnight
07/01/2005". Or, in SQL and using unambiguous date format:
WHERE Day >= '20050106'
AND Day < '20050107'

The other way is to make sure that the values in the Day column are
stripped of their time portion (or rather: that they all have the default
time portion 00:00:00.000). Since the values are filled in a CSharp
application, your best bet would be to investigate the builtin functions
of CSharp for this. Another way would be to do the inserts and updates
through a stored procedure that removes the time portion from the supplied
parameter before inserting or updating the row. If you can't use CSharp to
remove the time portion AND you can't use stored procedures to tidy up the
data, the last resort is to use a trigger - but only use this as a last
resort, since I don't really like the concept of inserting "dirty" data
that gets tidied up under the covers.

If you use a stored procedure (or a trigger) to remove the time portion,
you can use the expression below. The trick used is to calculate the
number of days between a fixed date (any date will do) and the datetime
supplied, than add this number of days to the same fixed date. In the
example below, I use CURRENT_TIMESTA MP - replace this with the name of
your datetime column or datetime parameter.
SELECT DATEADD(day,
DATEDIFF(day, '20040101', CURRENT_TIMESTA MP),
'20040101')

You can use the same expression if you need to remove the time part from
the data already present in your table:
UPDATE MyTable
SET MyDate = DATEADD(day,
DATEDIFF(day, '20040101', MyDate),
'20040101')

Don't forget to check out Tibor Karaszi's artivel about the datetime
datatype: http://www.karaszi.com/SQLServer/info_datetime.asp.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[Marc Pelletier]

Re: querying by date on smalldatetime

Hugo Kornelis <hugo@pe_NO_rFa ct.in_SPAM_fo> wrote in
news:j3jst05269 723i7tleas49lle h5hrock7v@4ax.c om:
[color=blue]
> The other way is to make sure that the values in the Day column are
> stripped of their time portion (or rather: that they all have the
> default time portion 00:00:00.000). Since the values are filled in a
> CSharp application, your best bet would be to investigate the builtin
> functions of CSharp for this. Another way would be to do the inserts
> and updates
>[/color]

That's exactly what I thought I was doing. The .Date modifier used when
filling the row does put only the date part in, which is proved by the
fact that the following works.[color=blue]
>SELECT * FROM StationDays WHERE (Station_ID = 8) AND
>([Day] = CONVERT(DATETIM E, '2005-01-06 00:00:00', 102))[/color]

I have also tried the >= jan 6 and < jan 7 route, but it didn't work for
reasons I'm at a loss to explain.

I'll experiment again using different date formats. I used the one I did
because that is what sql server enterprise manager put in!

Thanks for your help.

Marc

[Hugo Kornelis]

Re: querying by date on smalldatetime

On Fri, 07 Jan 2005 14:20:41 -0000, Marc Pelletier wrote:
[color=blue]
>That's exactly what I thought I was doing. The .Date modifier used when
>filling the row does put only the date part in, which is proved by the
>fact that the following works.[color=green]
>>SELECT * FROM StationDays WHERE (Station_ID = 8) AND
>>([Day] = CONVERT(DATETIM E, '2005-01-06 00:00:00', 102))[/color]
>
>I have also tried the >= jan 6 and < jan 7 route, but it didn't work for
>reasons I'm at a loss to explain.
>
>I'll experiment again using different date formats. I used the one I did
>because that is what sql server enterprise manager put in![/color]

Hi Marc,

Apologies - I misread your post. The asnwer to your question WAS included
in my post, but only as a small remark between parentheses.

You need to switch to an unambiguous date format. Your query uses
06/01/2005. For you (and me), it only seems logical that this date denotes
6 january 2005, but in many countries, this would be read as the June 1st
2005 instead. Your SQL Server is obviously using the latter
interpretation.

The following date and datetime formats are the ONLY formats that are
guaranteed to be always interpreted correctly:

* For date only: yyyymmdd. Note: no interpunction, just 8 numbers.
* For date plus time: yyyy-mm-ddThh:mm:ss.ttt . Note: this one DOES require
interpunction, plus a capital T between the date and the time part. The
milliseconds (.ttt) may be left out.
* For time only: hh:mm:ss.ttt. Note: the milliseconds (.ttt) may be left
out.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[Erland Sommarskog]

Re: querying by date on smalldatetime

Hugo Kornelis (hugo@pe_NO_rFa ct.in_SPAM_fo) writes:[color=blue]
> You need to switch to an unambiguous date format. Your query uses
> 06/01/2005. For you (and me), it only seems logical that this date denotes
> 6 january 2005, but in many countries, this would be read as the June 1st
> 2005 instead.[/color]

No, in about all countries in this world, this date is not likely to be
read as anything else than Jan 6th. I only know of one country where they
insist having it backwards.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Hugo Kornelis]

Re: querying by date on smalldatetime

On Fri, 7 Jan 2005 23:09:50 +0000 (UTC), Erland Sommarskog wrote:
[color=blue]
>Hugo Kornelis (hugo@pe_NO_rFa ct.in_SPAM_fo) writes:[color=green]
>> You need to switch to an unambiguous date format. Your query uses
>> 06/01/2005. For you (and me), it only seems logical that this date denotes
>> 6 january 2005, but in many countries, this would be read as the June 1st
>> 2005 instead.[/color]
>
>No, in about all countries in this world, this date is not likely to be
>read as anything else than Jan 6th. I only know of one country where they
>insist having it backwards.[/color]

Hi Erland,

Backwards? Are you trying to get a flame war started here? <g>

Unfortunately, this one country happens to be the most influential in
computing at the moment AND the country where SQL Server is made, so I
expect the "backwards" notation to remain the default setting for some
time to come...

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[Marc Pelletier]

Re: querying by date on smalldatetime

Hugo Kornelis <hugo@pe_NO_rFa ct.in_SPAM_fo> wrote in
news:c77tt0pnk4 lcqh4qtuv2kqjg4 r4ksl291m@4ax.c om:
[color=blue]
> * For date only: yyyymmdd. Note: no interpunction, just 8 numbers.
>[/color]

hmm... with the following query:
SELECT *
FROM StationDays
WHERE (Station_ID = 8) AND ([Day] = 20050106)

I get an "Arithmetic overflow error converting expression to data type
smalldatetime" exception. Both in the enterprise manager and in code.

Changing the field name to something less ambiguous doesn't help, neither
does changing the fieldtype to DateTime.

This DOES work, and is no more effort to code.
SELECT *
FROM StationDays
WHERE (Station_ID = 8) AND ([Day] = 'Jan 6, 2005')

Go figure, SQL Server is an unfathomable mystery to me.

Thanks

Marc Pelleteri

[Erland Sommarskog]

Re: querying by date on smalldatetime

Marc Pelletier (no.email@pleas e.com) writes:[color=blue]
> Hugo Kornelis <hugo@pe_NO_rFa ct.in_SPAM_fo> wrote in
> news:c77tt0pnk4 lcqh4qtuv2kqjg4 r4ksl291m@4ax.c om:
>[color=green]
>> * For date only: yyyymmdd. Note: no interpunction, just 8 numbers.
>>[/color]
>
> hmm... with the following query:
> SELECT *
> FROM StationDays
> WHERE (Station_ID = 8) AND ([Day] = 20050106)
>
> I get an "Arithmetic overflow error converting expression to data type
> smalldatetime" exception. Both in the enterprise manager and in code.[/color]

Hugo made the mistake that you would understand that the date should
be a string. Correct is:

SELECT *
FROM StationDays
WHERE (Station_ID = 8) AND ([Day] = '20050106')

As a number, the interpretation is that many days from 1900-01-01, which
does fit into a smalldatetime.
[color=blue]
> Changing the field name to something less ambiguous doesn't help, neither
> does changing the fieldtype to DateTime.
>
> This DOES work, and is no more effort to code.
> SELECT *
> FROM StationDays
> WHERE (Station_ID = 8) AND ([Day] = 'Jan 6, 2005')[/color]

It works until you do something like:

SET LANGUAGE Italian

'20050106' works with any setting for language and dateformat.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp