Solved

Problem with SQL 2005 Date Statement

Posted on 2009-07-06
4
260 Views
Last Modified: 2012-05-07
Here is the Statement,
ALTER PROCEDURE [dbo].[p_ST_TimeSheet_SelectByPersonAndDate]
      @p_PersonID int,
      @p_StartDate DateTime,
      @p_FinishDate DateTime
AS
SELECT TimeSheetID, TimesheetGUID, PersonID, PersonGUID, JobID, JobGUID, JobNumber, StartDateTime, FinishDateTime, Activity, Note,
      UpdatedDate, UpdatedBy, CreatedDate, CreatedBy, isDurationPeriod, isChargeable, IsPostedToJobSheet
FROM t_ST_TimeSheet
WHERE PersonID = @p_PersonID AND StartDateTime >= @p_StartDate AND FinishDateTime < @p_FinishDate

When I try to execute
p_ST_TimeSheet_SelectByPersonAndDate 3,'01/01/1700','07/08/2001'
I get the error
Msg 8114, Level 16, State 1, Procedure p_ST_TimeSheet_SelectByPersonAndDate, Line 0
Error converting data type varchar to datetime.
Any date year >1900 is fine.  But could someone tell me how to correct this SQL statement please?
0
Comment
Question by:deeznutz
4 Comments
 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 500 total points
ID: 24789172
The earliest date you can pass to a datetime is January 1, 1753.
0
 
LVL 15

Expert Comment

by:mohan_sekar
ID: 24789192
I guess '01/01/1700' is out of range for datetime fields - min value is 01/01/1753!
0
 
LVL 17

Expert Comment

by:pssandhu
ID: 24789227
If in SQL Server 2005 the minimum date is 1st January 1753, in SQL Server 2008 we have a new data type with minimum date 1st January 0001.
Also, check this article out on how to extend dates to include dataes less than 1753-01-01:
http://weblogs.sqlteam.com/mladenp/archive/2006/12/16/52754.aspx
Hope this helps.
P.
0
 
LVL 1

Author Closing Comment

by:deeznutz
ID: 31600356
Thanks for all responses but seeing as this was the first answer I must award points here as they are basically the same. :)
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
sql 2014,  lock limit 5 32
Help creating a spatial object in SQL Server 4 22
SQL Error - Query 6 26
How to debug a store procedure in MS SQL 2008? 3 10
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

772 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