Link to home
Start Free TrialLog in
Avatar of asrisbey
asrisbey

asked on

SQL 2005 DateTime syntax for WHERE clause

I have the following query:

SELECT dbo.CONTENT_Event.eventID, dbo.CONTENT_Event.EventName + ' (' + EventOrgName + ')'
FROM dbo.View_CMS_Tree_Joined_Versions
INNER JOIN dbo.CONTENT_Event
ON View_CMS_Tree_Joined_Versions.DocumentForeignKeyValue = dbo.CONTENT_Event.[eventID]
WHERE ...

I want a WHERE condition to return events for the current date and all future dates (ie. exclude past events), based on the dbo.CONTENT_Event.EventDate field (datetime).

Can anyone help out with the syntax?

Thanks.
Avatar of Ashish Patel
Ashish Patel
Flag of India image

SELECT dbo.CONTENT_Event.eventID, dbo.CONTENT_Event.EventName + ' (' + EventOrgName + ')'
FROM dbo.View_CMS_Tree_Joined_Versions
INNER JOIN dbo.CONTENT_Event
ON View_CMS_Tree_Joined_Versions.DocumentForeignKeyValue = dbo.CONTENT_Event.[eventID]
WHERE dbo.CONTENT_Event.EventDate >= Cast(Convert(varchar(10), GetDate(), 103) as DateTime)
Along with your previous question the whole query should look like this.

SELECT dbo.CONTENT_Event.eventID, dbo.CONTENT_Event.EventName + ' (' + EventOrgName + ')' + Convert(varchar(10), dbo.CONTENT_Event.EventDate, 103)
FROM dbo.View_CMS_Tree_Joined_Versions
INNER JOIN dbo.CONTENT_Event
ON View_CMS_Tree_Joined_Versions.DocumentForeignKeyValue = dbo.CONTENT_Event.[eventID]
WHERE dbo.CONTENT_Event.EventDate >= Cast(Convert(varchar(10), GetDate(), 103) as DateTime)
Avatar of asrisbey
asrisbey

ASKER

OK, I've added the WHERE clause and the query now returns no results. However, if I change 103 to 101 it returns the results I would expect. This is how the data is stored in the table '13/04/2008 10:00:00' ie. UK date format, non of this American stuff!

Does that make sense? And is that an acceptable change to the WHERE clause?

Thanks again.
try this

SELECT dbo.CONTENT_Event.eventID, dbo.CONTENT_Event.EventName + ' (' + EventOrgName + ')'
FROM dbo.View_CMS_Tree_Joined_Versions
INNER JOIN dbo.CONTENT_Event
ON View_CMS_Tree_Joined_Versions.DocumentForeignKeyValue = dbo.CONTENT_Event.[eventID]
WHERE dbo.CONTENT_Event.EventDate >= CAST(FLOOR(CAST( GETDATE() AS FLOAT)) AS DATETIME)
ASKER CERTIFIED SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Oh yes, that should not make any difference
so either use either of the query

SELECT dbo.CONTENT_Event.eventID, dbo.CONTENT_Event.EventName + ' (' + EventOrgName + ')' + Convert(varchar(10), dbo.CONTENT_Event.EventDate, 103)
FROM dbo.View_CMS_Tree_Joined_Versions
INNER JOIN dbo.CONTENT_Event
ON View_CMS_Tree_Joined_Versions.DocumentForeignKeyValue = dbo.CONTENT_Event.[eventID]
WHERE dbo.CONTENT_Event.EventDate >= Cast(Convert(varchar(10), GetDate(), 101) as DateTime)

Or

SELECT dbo.CONTENT_Event.eventID, dbo.CONTENT_Event.EventName + ' (' + EventOrgName + ')' + Convert(varchar(10), dbo.CONTENT_Event.EventDate, 103)
FROM dbo.View_CMS_Tree_Joined_Versions
INNER JOIN dbo.CONTENT_Event
ON View_CMS_Tree_Joined_Versions.DocumentForeignKeyValue = dbo.CONTENT_Event.[eventID]
WHERE Cast(Convert(varchar(10), dbo.CONTENT_Event.EventDate, 103) as DateTime) >= Cast(Convert(varchar(10), GetDate(), 103) as DateTime)
asvforce

Sorry, the 2nd solution gives me...
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

angellll, I'll go with that, seems to work fine. Thanks.