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?
 
k_murli_krishnaConnect With a Mentor Commented:
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.