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

SQL Server 2005 Forums

Transact-SQL (2005)

query-datetime datatype

Author

Topic

shm
Yak Posting Veteran

86 Posts

Posted - 2008-08-05 : 00:47:26

hi,

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..

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 : 00:57:47

Because your calculation is coming up with 01/32/2008. You need to have valid dates each time you convert to datetime.

The problem is right here:
CONVERT(VARCHAR(10),DATEPART(DAY,@Date)+1)

Notice how if you added 1 to 31 (or 30), you'd get an invalid date.

But you shouldn't have T-SQL code like this. You need to explain exactly what you are trying to do with the input parameter. Ignore the rest of the code and just describe before_last and last aliased columns.

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

Subscribe to my blog

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-05 : 00:58:44

Try putting SET DATEFORMAT mdy before the query and running it

tkizer
Almighty SQL Goddess

38200 Posts

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

SET DATEFORMAT mdy still fails. You can see the issue here:


set dateformat dmy

declare @Date datetime
set @Date = '03/31/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

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

Subscribe to my blog

shm
Yak Posting Veteran

86 Posts

Posted - 2008-08-05 : 01:19:44

ok.
i tried then also it is giving error and also null values are displaying.
And also if i try to give the date '01/20/2008' then also it is giving error.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-08-05 : 01:20:52

See my first post in this 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