470,612 Members | 2,437 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,612 developers. It's quick & easy.

SQL Help???

Hi,

i am trying to run a simple SQL statement from my C# app to return a number
of records, however when i do my record count is always zero, but i know
there are transactions in there... My code is as follows...

"SELECT * FROM " + Table + " WHERE Date >=" + "#" +
DateFrom.ToShortDateString() + "#" + " And " + " Date <=" + "#" +
DateTo.ToShortDateString() + "#"

I don't get any exceptions or other warnings... the code executes fine...
but my records returned is always zero??? i can't work it out!

Any help would be appreciated.

Regards

Darryn
Nov 16 '05 #1
4 1083
Darryn Ross wrote:
Hi,

i am trying to run a simple SQL statement from my C# app to return a
number of records, however when i do my record count is always zero,
but i know there are transactions in there... My code is as follows...

"SELECT * FROM " + Table + " WHERE Date >=" + "#" +
DateFrom.ToShortDateString() + "#" + " And " + " Date <=" + "#" +
DateTo.ToShortDateString() + "#"

I don't get any exceptions or other warnings... the code executes
fine... but my records returned is always zero??? i can't work it out!

Any help would be appreciated.

Regards

Darryn


This is for Access, right? (the #date# syntax is Access-specific).
Could it be that you write the date in dd-mm-yyyy format, but it
is interpreted as mm-dd-yyyy (or the other way around)?

What if you print the sql-string and execute that directly in Access?
Then again, maybe if *you* execute it, Access follows *your*
international settings. If the aspnet user executes it, Acces might
follow *it's* settings.

I personally never like "string to date" conversions (what format is used?).
It might be better to use real parameters where you can use the real
DateTime value.

Hans Kesting
Nov 16 '05 #2
Hi Darryn,

I may not be much help here, because I don't even know what the "#"
signs actually mean. I don't think you want to send them to SQL. I'm
better at SQL than C#, but I do know both.

Anyway, my guess is that the date field you are querying on has a
non-zero time portion. So, for example, if you have a field like
this:

Transaction_Date
----------------
11/01/2004 10:30:45

Then, a query asking to return all records WHERE Transaction_Date =
'11/01/2004' will absolutely NOT return this record. Although, I
think some databases might allow you to setup your date/time defaults
to allow that behavior (I'm not sure about that part).

Anyway, this is a very common problem, and in practice, there are
several ways around this, and depending on your environment and
requirements, you can pick from these. There may be other options.
Let me know if you find a better one.

Option 1 - Eliminate the time portion from the comparison

- Oracle: WHERE TRUNC(Transaction_Date) =
TO_DATE('11/01/2004','MM/DD/YYYY')
- SQL Server: WHERE CONVERT(varchar,Transaction_Date,111) =
'2004/11/01'

I'm more familiar with the Oracle fix here, and have not yet been able
to find a better way in SQL Server. The 111 is a style parameter that
expects the YYYY/MM/DD format on the right side. There is a
corresponding format mask in C# .NET. You could also convert to a
date, but that may be too much overhead. Using the 111 allows a
simple string comparison to actually work because you send the year,
then the month, then the day, which ensures a proper comparison in
cases where a simple equals (i.e. =) comparison is not being used.
That is, you wouldn't want to compare a string in the form DD/MM/YYYY
to a >= operator. You'll get incorrect results.

You should note that both of these approaches will probably break
(i.e. not use) your indexes, potentially causing slower performance.
This would be a problem in a data-warehouse environment, but not in an
operational system. There are ways around that too, possibly
including function-based indexes. I haven't gotten around to trying
that too much.

Option 2 - Don't store the time portion OR Store it separately

Dates and times cannot usually be stored independently, at least not
in a 'datetime' field. That is, you usually need them both together.
A time can not be stored without it's date. From what I've read, if
you have a data-warehouse environment with lots of data where using an
index is important (and therefore option 1 would not really be smart),
then some people recommend storing the value in two separate fields.
One would be the date, with a 00:00:00 time (for searching), the other
would be the date and the actual time (for display). I have also seen
the time stored in a char(5) field. Either way would work.

Good luck and let me know if this helped or hurt you.

-Ryan
"Darryn Ross" <da****@datawave.com.au> wrote in message news:<e#*************@TK2MSFTNGP09.phx.gbl>...
Hi,

i am trying to run a simple SQL statement from my C# app to return a number
of records, however when i do my record count is always zero, but i know
there are transactions in there... My code is as follows...

"SELECT * FROM " + Table + " WHERE Date >=" + "#" +
DateFrom.ToShortDateString() + "#" + " And " + " Date <=" + "#" +
DateTo.ToShortDateString() + "#"

I don't get any exceptions or other warnings... the code executes fine...
but my records returned is always zero??? i can't work it out!

Any help would be appreciated.

Regards

Darryn

Nov 16 '05 #3
Hans,

I ended up working it out, like you said Access requires Us date time
formats so i was sending dd/mm/yyyy when it requires mm/dd/yyyy?? stupid but
it's fine now anyway.

Thanks

Darryn

"Hans Kesting" <ne***********@spamgourmet.com> wrote in message
news:e0**************@tk2msftngp13.phx.gbl...
Darryn Ross wrote:
Hi,

i am trying to run a simple SQL statement from my C# app to return a
number of records, however when i do my record count is always zero,
but i know there are transactions in there... My code is as follows...

"SELECT * FROM " + Table + " WHERE Date >=" + "#" +
DateFrom.ToShortDateString() + "#" + " And " + " Date <=" + "#" +
DateTo.ToShortDateString() + "#"

I don't get any exceptions or other warnings... the code executes
fine... but my records returned is always zero??? i can't work it out!

Any help would be appreciated.

Regards

Darryn
This is for Access, right? (the #date# syntax is Access-specific).
Could it be that you write the date in dd-mm-yyyy format, but it
is interpreted as mm-dd-yyyy (or the other way around)?

What if you print the sql-string and execute that directly in Access?
Then again, maybe if *you* execute it, Access follows *your*
international settings. If the aspnet user executes it, Acces might
follow *it's* settings.

I personally never like "string to date" conversions (what format is

used?). It might be better to use real parameters where you can use the real
DateTime value.

Hans Kesting

Nov 16 '05 #4
Hans,

I ended up working it out, like you said Access requires Us date time
formats so i was sending dd/mm/yyyy when it requires mm/dd/yyyy?? stupid but
it's fine now anyway.

Thanks

Darryn

"Hans Kesting" <ne***********@spamgourmet.com> wrote in message
news:e0**************@tk2msftngp13.phx.gbl...
Darryn Ross wrote:
Hi,

i am trying to run a simple SQL statement from my C# app to return a
number of records, however when i do my record count is always zero,
but i know there are transactions in there... My code is as follows...

"SELECT * FROM " + Table + " WHERE Date >=" + "#" +
DateFrom.ToShortDateString() + "#" + " And " + " Date <=" + "#" +
DateTo.ToShortDateString() + "#"

I don't get any exceptions or other warnings... the code executes
fine... but my records returned is always zero??? i can't work it out!

Any help would be appreciated.

Regards

Darryn
This is for Access, right? (the #date# syntax is Access-specific).
Could it be that you write the date in dd-mm-yyyy format, but it
is interpreted as mm-dd-yyyy (or the other way around)?

What if you print the sql-string and execute that directly in Access?
Then again, maybe if *you* execute it, Access follows *your*
international settings. If the aspnet user executes it, Acces might
follow *it's* settings.

I personally never like "string to date" conversions (what format is

used?). It might be better to use real parameters where you can use the real
DateTime value.

Hans Kesting

Nov 16 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

9 posts views Thread by Tom | last post: by
6 posts views Thread by wukexin | last post: by
3 posts views Thread by Colin J. Williams | last post: by
7 posts views Thread by Corepaul | last post: by
5 posts views Thread by Steve | last post: by
8 posts views Thread by Mark | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.