Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old March 15th, 2004, 08:23 PM
Authorized User
 
Join Date: Jul 2003
Location: , , .
Posts: 98
Thanks: 0
Thanked 0 Times in 0 Posts
Default Date Query Problem

Hello Everyone and thanks for your help in advance. I am working on a application that passes parameters to a stored procedure similar to the following:

SELECT * From [tblVisits]

WHERE
    (CheckedIn BETWEEN @BeginningDate
    AND @EndingDate)

If today's date were the parameters, I would pass the following:

BeginningDate As DateTime = "3/15/2004 12:00:00 am"
EndingDate As DateTime = "3/15/2004 12:59:59 pm"

However, this only returns time prior to 1:00 pm. When I run the SPROC in Query Analyzer, I get the same results. But when I cahnge the ending time to 23:59:59 pm, it returns all of the records. In examining the databse, the dates are stored in the 12 hour format rather than the 24 hour format. I am not sure why this is occurring. Any help would be greatly appreciated.

 
Old March 15th, 2004, 10:51 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Bangalore, KA, India.
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

I don't think you can use something like this "23:59:59 pm" as it does not seem to be a valid time format. When you say 23:59:59, you should not specipy "pm" there.

As you said the dates are stored in 12 hrs format, that is how the SQLServer stores the date values. But it provides you with a lot other formats with which you can query up on the date columns. Convert function is used to get any of those formats.

For example, when you want to pass the date values in 24hrs format as parameters, you can always use yyyy-mm-dd hh:mm:ss.mss format in your case, where value for hh can range from 00 - 23.

BeginningDate As DateTime = "2004-03-15 12:00:00.000"
EndingDate As DateTime = "2004-03-15 12:59:59.000"

eg:
Select Convert(varchar(25),getdate(),121) - gives you the format that is specified above.

Hope that helps.

Cheers,

-Vijay G
 
Old March 18th, 2004, 04:31 PM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

12:59:59pm is one second before 1pm. You need to change this end time to 11:59:59pm, that is one second before 12:00:00am.

I bet you push the door marked pull :-)



mmcdonal




Similar Threads
Thread Thread Starter Forum Replies Last Post
date query problem naveed77 Beginning VB 6 5 March 4th, 2008 05:43 PM
Problem in Date Format in query imagetvr Pro VB 6 1 February 4th, 2008 06:03 PM
problem with the parameter date in a mdx query olmouy SQL Language 0 May 30th, 2007 05:32 AM
Help! Date query Problem yikchin Access 3 November 22nd, 2005 05:39 PM
Convert String Date to Date for a SQL Query tdaustin Classic ASP Basics 4 July 7th, 2003 06:01 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.