Why does this datetime between not work?

Posted on 2005-04-30
Last Modified: 2010-03-19
Hi folks,

What am I missing here? My sp is supposed to return only the records from the table where today's date (represented by GetDate()) is between the [PhaseStartDate] and [PhaseEndDate].

Here's the SQL I'm using:

SELECT PhaseID,PhaseStartDate,PhaseEndsDate FROM tb_phases
WHERE GetDate() BETWEEN PhaseStartDate AND PhaseEndsDate

Looks simple enough, but it doesn't appear to be doing any 'BETWEEN' stuff at all.

Dates in my table are in the format 103 (dd/mm/yyyy) but I can easily convert these to UTC if it's wiser?

Sample table data:

1,#29/04/2005 10:00:00#,#10/05/2005 23:00:00#
2,#20/04/2005 10:00:00#,#10/05/2005 23:00:00#


When I run this query, it returns both phases, even though today is 30/04/2005.

What gives?!


Question by:OnError_Fix
    LVL 8

    Accepted Solution

    >>Dates in my table are in the format 103 (dd/mm/yyyy)

    This is a little confusing are you saying they are stored as strings?  Why not use datatime data type instead.

    That aside unless I'm going completely made it looks like your query should return both records??  Both phases end somewhere in may an both begin before 30th April.

    LVL 8

    Expert Comment

    OTH if you are storing strings there is no way for SQL to know the format, it will assume mm/dd/yyyy when coercing strings that use numbers for months.


    Author Comment

    Sorry --- they ARE in Date/Time datatype fields, only they are in the format of dd/mm/yy.

    With regards to the fact that it is returning both phases, I now feel like the world's biggest idiot :) That's what happens when you've been playing with datetimes for the past 4 hours solid!

    Sorry about that!

    One minor question tho, even though when I use query analyser, it returns the date in the format dd/mm/yy hh:mm:ss, there is no hh:mm:ss when I view the table in Enterprise Manager. Why's that?
    LVL 8

    Expert Comment

    The grid rendering engine is different between QA and EM.  Probably because at some time in the past one or both originated outside of Microsoft and were bought in.  The grid in EM for example also shows nulls explicitly as <NULL> where QA simple shows a blank.  If the date time has a non-zero time element the grid in EM does show it.


    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    Join & Write a Comment

    Suggested Solutions

    Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
    Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
    This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
    Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

    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

    18 Experts available now in Live!

    Get 1:1 Help Now