?
Solved

Why does this datetime between not work?

Posted on 2005-04-30
4
Medium Priority
?
189 Views
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:

PhaseID,PhaseStartDate,PhaseEndsDate
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#

*** PLEASE NOTE: THE TIME IS IMPORTANT! ***

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

What gives?!

Thanks,

OnError_Fix
0
Comment
Question by:OnError_Fix
  • 3
4 Comments
 
LVL 8

Accepted Solution

by:
anthonywjones66 earned 2000 total points
ID: 13900364
>>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.

Anthony.
0
 
LVL 8

Expert Comment

by:anthonywjones66
ID: 13900367
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.

Anthony.
0
 

Author Comment

by:OnError_Fix
ID: 13900371
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?
0
 
LVL 8

Expert Comment

by:anthonywjones66
ID: 13900403
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.

Anthony.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
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.
Suggested Courses

830 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