473,574 Members | 2,687 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

querying by date on smalldatetime

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
Jul 23 '05 #1
7 32932
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.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?


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)
Jul 23 '05 #2
Hugo Kornelis <hugo@pe_NO_rFa ct.in_SPAM_fo> wrote in
news:j3******** *************** *********@4ax.c om:
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(DATETIM E, '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
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(DATETIM E, '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
Hugo Kornelis (hugo@pe_NO_rFa ct.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****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #5
On Fri, 7 Jan 2005 23:09:50 +0000 (UTC), Erland Sommarskog wrote:
Hugo Kornelis (hugo@pe_NO_rFa ct.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
Hugo Kornelis <hugo@pe_NO_rFa ct.in_SPAM_fo> wrote in
news:c7******** *************** *********@4ax.c om:
* 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
Marc Pelletier (no******@pleas e.com) writes:
Hugo Kornelis <hugo@pe_NO_rFa ct.in_SPAM_fo> wrote in
news:c7******** *************** *********@4ax.c om:
* 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****@sommarsk og.se

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

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

10
8169
by: Fawke101 | last post by:
Hi there, I have a date field in SQL server - that holds dates as DD/MM/YYYY format (GB). Now, i have an ASP application that Adds/Edits records in this table; and i am having real problems with the date field in the ADD and EDIT (update) part for this app. Basically, i receive and Error as below:
4
8095
by: DCM Fan | last post by:
{CREATE TABLEs and INSERTs follow...} Gents, I have a main table that is in ONE-MANY with many other tables. For example, if the main table is named A, there are these realtionships: A-->B A-->C A-->D
8
5486
by: Edward | last post by:
SQL Server 7.0 The following SQL: SELECT TOP 100 PERCENT fldTSRID, fldDateEntered FROM tblTSRs WITH (NOLOCK) WHERE ((fldDateEntered >= CONVERT(DATETIME, '2003-11-21 00:00:00', 102)) AND (fldDateEntered <= CONVERT(DATETIME, '2003-11-23 23:59:59', 102)))
18
10260
by: Robin Lawrie | last post by:
Hi again, another problem! I've moved from an Access database to SQL server and am now having trouble inserting dates and times into seperate fields. I'm using ASP and the code below to get the date and time, but my script is erroring. '-- Get login date and time cmdLoginDate = Date() cmdLoginTime = Time()
3
1965
by: Sam | last post by:
Hi, I store the date selected in a datetimepicker into a sqlserver table in a SmallDateTime field. To be compatible with the date format of my sql server, I convert the date as following: Dim d As Date Dim dInDate As Date Dim ciOriginal As cultureinfo
5
3047
by: Øyvind Isaksen | last post by:
I have a page with an optional integer-field, and one asp:calendar control. I use a stored procedure to save the data in SQL Server. When all fields contains data, the code works great! But if the user dont fill in the optional "price-field" (integer value), or if the user dont choose a date in the asp:calendar control, I get the message...
3
4037
by: noone | last post by:
Hi, I am designing an application which displays news topics until midnight on the DisplayUntil date and then they should drop out. Unfortunately, they seem to be dropping out at mid-day. I'm storing the 'DisplayFrom' and 'DisplayUntil' dates as SmallDateTime fields so the date is in the format "25/09/2006 00:0:00" and I'm comparing them...
2
5752
by: MackTheKnife | last post by:
Hi, I'm trying to write a java.sql.Date to a database and the minutes/ seconds etc. are not being written. I've seen and tested many examples found via searches and still have come up with nothing. Here's what I have: java.sql.Date formation - recommended constructor forms: java.sql.Date entryDttm = new...
1
1838
by: DJo | last post by:
I am trying to insert a set of date variables to use in a stored proc that will automatically select the last 12 running months worth of data grouped monthly. This code stops at the end of the prior year. and grabs data in the current month of the current year. - See example at end If I use the @endDate variable, it fails as the enddate...
0
7732
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
8243
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
1
7822
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
8101
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
0
6456
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
0
5302
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3742
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
0
3754
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
0
1062
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.