sql statement :: datetime filtering

Posted on 2008-11-16
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

    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

    thanks :-)
    LVL 39

    Expert Comment

    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

    Live: Real-Time Solutions, Start Here

    Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

    Join & Write a Comment

    This document covers how to connect to SQL Server and browse its contents.  It is meant for those new to Visual Studio and/or working with Microsoft SQL Server.  It is not a guide to building SQL Server database connections in your code.  This is mo…
    A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
    In this sixth video of the Xpdf series, we discuss and demonstrate the PDFtoPNG utility, which converts a multi-page PDF file to separate color, grayscale, or monochrome PNG files, creating one PNG file for each page in the PDF. It does this via a c…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    755 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

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now