sql statement :: datetime filtering

:: The problem

datetime filter is returning for example only 3 records that are dated today or earlier -- yet I know there are 6 applicable records.

I suspect -- the 1st records may have been recorded in the database in the us-format  month, day, year etc.. and all the records are being recorded uk format,  day, month, year -- yet, browsing through the actual db records, all datetime's look the same structure visually -- but that might just be the visual studio displaying in a consistent format automatically in that view type.

I have a datagrid that filters just fine -- and I am seeing all 6 records as expected.

The sql statement using the   Label_Date.Text = DateTime.Now.ToString("ddd, MMM dd, yyyy");   to compare with as follows:

.....
AND (DateTimeCallBack <= @SystemDate+1)

NOW, I have another sql statement as follows -- which is only returning the 3 records ::

.....
AND DateTimeCallBack < GETDATE()

this db call is actioned from a WinForm (whereas the the example is called via an aspx page).

what am I missing please?  is this month/day the issue to hand?

amillyardAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

k_murli_krishnaCommented:
Sometimes, you can store in different formats converted from string to date while inserting/updating. But ultimately date gets stored in one particular format i.e. the one you get when at OS command line you issue SELECT GETDATE() after connecting to database. But you can again retrieve in different formats using built-in function to convert date to string using formatting.

Label_Date.Text = DateTime.Now.ToString("ddd, MMM dd, yyyy");   Check whether this a recognized insert/update OR select format.

AND (DateTimeCallBack <= @SystemDate+1)
If the system date does contain date of DB server, then you are saying <= tommorow.

AND DateTimeCallBack < GETDATE()
Now you are saying < today.
Please check on these points. It can be a month and date reversal issue. In that case try using >12 i.e. 13 for both date and month to determine which is month if error is thrown during insert/update OR select.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
amillyardAuthor Commented:
thanks :-)
0
BrandonGalderisiCommented:
AND DateTimeCallBack < GETDATE()

getdate() is the equivilant of NOW() in most languages.  It is the current date and TIME on the SQL Server.

I assume that datetimecallback is a datetime field?  If it's a varchar field (which it shouldn't be) then you may have some issues with implicit conversion of data in the table causing issues.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.