query-datetime datatype - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

Reporting Services Development

query-datetime datatype

Author

Topic

shm
Yak Posting Veteran

86 Posts

Posted - 2008-08-05 : 00:59:40

hi,

i have one more doubt,by mistake i asked this in the transact-sql forum.

declare @Date datetime
set @Date = '03/29/2008'
declare @PRJ_SEQ_NO int
Set @PRJ_SEQ_NO = 302

SELECT REPLACE ( CONVERT (CHAR,CAST( CONVERT(VARCHAR(10),(DATEPART(MONTH,@Date)-2)) + '/' +CONVERT(VARCHAR(10),DATEPART(DAY,@Date)+1) + '/' + CONVERT(VARCHAR(5),DATEPART(YEAR,@Date)) AS DATETIME ),106) ,' ','-') AS before_last,
REPLACE (CONVERT (CHAR,CAST ( CONVERT(VARCHAR(10),DATEPART(MONTH,@Date)-1) + '/' + CONVERT(VARCHAR(10),(DATEPART(DAY,@Date))) + '/' + CONVERT(VARCHAR(5),DATEPART(YEAR,@Date)) AS DATETIME ),106 ), ' ', '-') AS last,
SUM(PLANNED_EFFORT) planned_effort,
SUM(ACTUAL_WORK) actual_work,
--CASE WHEN SUM(PLANNED_EFFORT) = 0 THEN 0 ELSE ISNULL ((SUM(ACTUAL_WORK)-SUM(PLANNED_EFFORT))/NULLIF(SUM(PLANNED_EFFORT),0),0 ) END AS EFFORT
CASE WHEN SUM(PLANNED_EFFORT) = 0 THEN 0 ELSE ((SUM(ACTUAL_WORK)-SUM(PLANNED_EFFORT))/NULLIF(SUM(PLANNED_EFFORT),0) ) END AS EFFORT
FROM

(SELECT PTK_SEQ_NO,
CASE WHEN PT.PTK_ACT_HOURS = '' THEN 0 ELSE ISNULL(CONVERT(decimal(14,0),PT.PTK_ACT_HOURS),0) END AS PLANNED_EFFORT,
SUM( PLT.PLT_HRS) AS ACTUAL_WORK

FROM PROJECT_TASK PT
INNER JOIN PROJECT_MODULE PM ON PT.PMO_MOD_SEQ_NO = PM.PMO_MOD_SEQ_NO
INNER JOIN PROJECT_PHASE PH ON PM.PPH_SEQ_NO = PH.PPH_SEQ_NO
INNER JOIN PROJECT P ON PH.PRJ_SEQ_NO = P.PRJ_SEQ_NO
LEFT OUTER JOIN PROJECT_LOG_TIME PLT ON PLT.PLT_OBJECT = PT.PTK_SEQ_NO

WHERE P.PRJ_SEQ_NO = @PRJ_SEQ_NO
AND PT.EST_START_DATE BETWEEN DATEADD(MONTH,-2,(@Date+1)) AND DATEADD(MONTH,-1, @Date)
GROUP BY PTK_SEQ_NO, PTK_ACT_HOURS )A

In one dataset the above query is placed..and date and project are the parameters am passing.
The error is coming when the date parameter is '03/31/2008' am not geting y this error is coming.

For any month if the last date is selected the error is coming in the report.The date parameter is datetime in report parameter and the user can select the date and it wil not allow null value.

I changed diff parameter as smalldatetime,date,datetime2 in query but it is also giving error.

The error is
Msg 242, Level 16, State 3, Line 7
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

help me in this plz..

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-08-05 : 01:02:37

Duplicate, locking thread.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources