By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,563 Members | 1,066 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,563 IT Pros & Developers. It's quick & easy.

querying by date on smalldatetime

P: n/a
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.NewRow();

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(DATETIME, '2005-01-06 00:00:00', 102))

What can I do to simplify this query?

Thanks

Marc Pelletier
Jul 23 '05 #1
Share this Question
Share on Google+
7 Replies


P: n/a
On Fri, 07 Jan 2005 04:16:28 -0000, Marc Pelletier wrote:
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.NewRow();

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(DATETIME, '2005-01-06 00:00:00', 102))

What can I do to simplify this query?


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_TIMESTAMP - replace this with the name of
your datetime column or datetime parameter.
SELECT DATEADD(day,
DATEDIFF(day, '20040101', CURRENT_TIMESTAMP),
'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)
Jul 23 '05 #2

P: n/a
Hugo Kornelis <hugo@pe_NO_rFact.in_SPAM_fo> wrote in
news:j3********************************@4ax.com:
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

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.SELECT * FROM StationDays WHERE (Station_ID = 8) AND
([Day] = CONVERT(DATETIME, '2005-01-06 00:00:00', 102))


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
Jul 23 '05 #3

P: n/a
On Fri, 07 Jan 2005 14:20:41 -0000, Marc Pelletier wrote:
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.
SELECT * FROM StationDays WHERE (Station_ID = 8) AND
([Day] = CONVERT(DATETIME, '2005-01-06 00:00:00', 102))


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!


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)
Jul 23 '05 #4

P: n/a
Hugo Kornelis (hugo@pe_NO_rFact.in_SPAM_fo) writes:
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.


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, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #5

P: n/a
On Fri, 7 Jan 2005 23:09:50 +0000 (UTC), Erland Sommarskog wrote:
Hugo Kornelis (hugo@pe_NO_rFact.in_SPAM_fo) writes:
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.


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.


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)
Jul 23 '05 #6

P: n/a
Hugo Kornelis <hugo@pe_NO_rFact.in_SPAM_fo> wrote in
news:c7********************************@4ax.com:
* For date only: yyyymmdd. Note: no interpunction, just 8 numbers.


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
Jul 23 '05 #7

P: n/a
Marc Pelletier (no******@please.com) writes:
Hugo Kornelis <hugo@pe_NO_rFact.in_SPAM_fo> wrote in
news:c7********************************@4ax.com:
* For date only: yyyymmdd. Note: no interpunction, just 8 numbers.

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.


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.
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')


It works until you do something like:

SET LANGUAGE Italian

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

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #8

This discussion thread is closed

Replies have been disabled for this discussion.