473,699 Members | 2,873 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 32962
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
8179
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
8103
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
5494
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
10294
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
1970
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 "Input string was not in a correct format". How do I save "Null" value if the price-field is blank,...
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 with GetDate() which (I suspect) includes the actual time as well.
2
5759
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 java.sql.Date(System.currentTimeMillis());
1
1841
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 return (month #)< begdate return (month #) If I do not use the @begdate, I get Jan of the @begyr -...
0
8703
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9185
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
8935
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 most users, this new feature is actually very convenient. If you want to control the update process,...
0
8893
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 choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
7773
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 launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
4389
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 the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4636
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3069
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
3
2015
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 effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.