Error message while trying to CAST date

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 2008 Forums

Other SQL Server 2008 Topics

Error message while trying to CAST date

Author

Topic

knichols
Starting Member

12 Posts

Posted - 2011-12-26 : 14:36:11

Hello all,

I am trying to get the date to behave:


SELECT COUNT(DWI.System_Id) AS 'ReleaseRegressionMediumBugCount'
FROM DimWorkItem DWI
(
 SELECT convert(varchar, getdate(), 101)
   INNER JOIN DimIteration DI
     ON DI.IterationSK = DWI.IterationSK
  INNER JOIN FactWorkItemLinkHistory FWI
     ON DATEADD(d,DATEDIFF(d,0,DI.LastUpdatedDateTime ),0) between
'12/09/2011' and '12/09/2011' = DATEADD(d, FWI
DATEDIFF(d,0,DI.LastUpdatedDateTime ),0) between '12/09/2011' and
'12/09/2011'
     INNER JOIN FactWorkItemLinkHistory FWI
        ON FactWorkItemLinkHistory FWILH = FWILH_TargetWorkItemID
     INNER JOIN FactWorkItemLinkHistory FWILH
        ON DWI.System_Id = FWILH.TargetWorkItemID
WHERE DI.IterationPath LIKE '\xxxxx%'
   AND DI.IterationPath NOT LIKE '\xxxxx\(_Bug Migration)%'
   AND DI.IterationPath NOT LIKE '\xxxxx\(Bug Inbox)%'
   AND DATEADD(d,DATEDIFF(d,0,FWILH.LastUpdatedDateTime),0) between
'12/09/2011' and '12/09/2011'
   AND DWI.System_WorkItemType = 'Bug'
   AND DWI.System_State <> 'Done'
   AND DWI.System_State <> 'Removed'
   AND DWI.Microsoft_VSTS_Common_Severity = '3 - Medium'
   AND DWI.System_Title NOT LIKE '%Pilot:%'
   AND DWI.System_Title NOT LIKE '%TechnicalDemonstrator%'
   AND DWI.System_Title NOT LIKE '%NonProductionCode%'
--AND DWI.System_State LIKE 'Committted'
--AND DWI.System_State LIKE 'New'
--AND DWI.System_State LIKE 'Approved'
--AND DWI.LastUpdatedDateTime = DI.LastUpdatedDateTime
   AND DWI.System_Rev) =
      (SELECT MIN (System_Rev)
       FROM DimWorkItem DWI_1
       WHERE DWI_1.System_Id = DWI_1.System_Id AND
       DWI_1.System_WorkItemType = 'Bug' AND DWI_1.TeamProjectSK = 159);

I am getting the following error messages:

Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'SELECT'.
Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'INNER'.

The code is also not recgonizing the d alias in the date syntax and it is not recgnizing the , in the getdate(), in the 4th line.

Any ideas?

Kurt

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-12-26 : 16:29:33

Your code doesn't make sense as it has too many syntax problems for us to even be able to figure this out. You can't do this for instance:

FROM DimWorkItem DWI (SELECT ... INNER JOIN...

That's just not how it works. Before the JOIN needs to be a FROM. Are you copying/pasting this from other bits of code?

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

Subscribe to my blog

knichols
Starting Member

12 Posts

Posted - 2011-12-26 : 16:39:10

Hello Tara,

I hope not to irritate you too badly this go around.

I have reworked it to this


SELECT COUNT(DWI.System_Id) AS 'ReleaseRegressionMediumBugCount' FROM DimWorkItem DWI
	INNER JOIN SELECT convert(varchar, getdate(), 101)
	INNER JOIN DimIteration DI ON DI.IterationSK = DWI.IterationSK 
	INNER JOIN FactWorkItemLinkHistory FWI ON DATEADD
	(SELECT (DATEDIFF(d,0,DI.LastUpdatedDateTime ),0) between '12/09/2011' and '12/09/2011' = DATEADD(d, FWI))
	DATEDIFF((d,0,DI.LastUpdatedDateTime ),0) between '12/09/2011' and '12/09/2011'
    INNER JOIN FactWorkItemLinkHistory FWI ON FactWorkItemLinkHistory FWILH = FWILH_TargetWorkItemID
     INNER JOIN FactWorkItemLinkHistory FWILH ON DWI.System_Id = FWILH.TargetWorkItemID
WHERE DI.IterationPath LIKE '\xxxx%'
   AND DI.IterationPath NOT LIKE '\xxxxx\(_Bug Migration)%'
   AND DI.IterationPath NOT LIKE '\xxxxx\(Bug Inbox)%'
   AND DATEADD(d,DATEDIFF(d,0,FWILH.LastUpdatedDateTime),0) between '12/09/2011' and '12/09/2011'
   AND DWI.System_WorkItemType = 'Bug'
   AND DWI.System_State <> 'Done'
   AND DWI.System_State <> 'Removed'
   AND DWI.Microsoft_VSTS_Common_Severity = '3 - Medium'
   AND DWI.System_Title NOT LIKE '%Pilot:%'
   AND DWI.System_Title NOT LIKE '%TechnicalDemonstrator%'
   AND DWI.System_Title NOT LIKE '%NonProductionCode%'
--AND DWI.System_State LIKE 'Committted'
--AND DWI.System_State LIKE 'New'
--AND DWI.System_State LIKE 'Approved'
--AND DWI.LastUpdatedDateTime = DI.LastUpdatedDateTime
   AND DWI.System_Rev) =
      (SELECT MAX (System_Rev)
       FROM DimWorkItem DWI_1
       WHERE DWI_1.System_Id = DWI_1.System_Id AND
       DWI_1.System_WorkItemType = 'Bug' AND DWI_1.TeamProjectSK = 159);

But now I am having problems on the INNER JOIN. I think what I need is a nested SQL and yes I am learning about them.

Additional note.

This code works but I need to use the date:

SELECT COUNT(DWI.System_Id) AS 'PilotRegressionSevBugCount' FROM DimWorkItem DWI
INNER JOIN DimIteration DI ON DI.IterationSK = DWI.IterationSK
WHERE DI.IterationPath LIKE '\xxxxx\G 4.0\%'
AND DI.IterationPath NOT LIKE '\xxxxx\(_Bug Migration)%'
AND DWI.System_WorkItemType = 'Bug'
AND DWI.System_State <> 'Done'
AND DWI.System_State <> 'Removed'
AND DWI.Microsoft_VSTS_Common_Severity = 'Regression'
AND DWI.System_Title LIKE '%Pilot:%'
AND DWI.System_Rev = (SELECT MAX(System_Rev) FROM DimWorkItem DWI_1
WHERE DWI_1.System_Id = DWI.System_Id AND DWI_1.System_WorkItemType = 'Bug' AND DWI_1.TeamProjectSK = 159);

Which is similar to the code above.

Hope this helps.

Kurt

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-12-26 : 17:13:59

I don't understand what you are trying to do with this: INNER JOIN SELECT convert(varchar, getdate(), 101)

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