[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


sql statement :: datetime filtering

Posted on 2008-11-16
Medium Priority
Last Modified: 2013-12-17
:: 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?

Question by:amillyard
LVL 17

Accepted Solution

k_murli_krishna earned 2000 total points
ID: 22972475
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.

Author Closing Comment

ID: 31517315
thanks :-)
LVL 39

Expert Comment

ID: 22972522
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.

Featured Post

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question