asrisbey
asked on
SQL 2005 DateTime syntax for WHERE clause
I have the following query:
SELECT dbo.CONTENT_Event.eventID, dbo.CONTENT_Event.EventNam e + ' (' + EventOrgName + ')'
FROM dbo.View_CMS_Tree_Joined_V ersions
INNER JOIN dbo.CONTENT_Event
ON View_CMS_Tree_Joined_Versi ons.Docume ntForeignK eyValue = 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.EventDat e field (datetime).
Can anyone help out with the syntax?
Thanks.
SELECT dbo.CONTENT_Event.eventID,
FROM dbo.View_CMS_Tree_Joined_V
INNER JOIN dbo.CONTENT_Event
ON View_CMS_Tree_Joined_Versi
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.EventDat
Can anyone help out with the syntax?
Thanks.
Along with your previous question the whole query should look like this.
SELECT dbo.CONTENT_Event.eventID, dbo.CONTENT_Event.EventNam e + ' (' + EventOrgName + ')' + Convert(varchar(10), dbo.CONTENT_Event.EventDat e, 103)
FROM dbo.View_CMS_Tree_Joined_V ersions
INNER JOIN dbo.CONTENT_Event
ON View_CMS_Tree_Joined_Versi ons.Docume ntForeignK eyValue = dbo.CONTENT_Event.[eventID ]
WHERE dbo.CONTENT_Event.EventDat e >= Cast(Convert(varchar(10), GetDate(), 103) as DateTime)
SELECT dbo.CONTENT_Event.eventID,
FROM dbo.View_CMS_Tree_Joined_V
INNER JOIN dbo.CONTENT_Event
ON View_CMS_Tree_Joined_Versi
WHERE dbo.CONTENT_Event.EventDat
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.
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.EventNam e + ' (' + EventOrgName + ')'
FROM dbo.View_CMS_Tree_Joined_V ersions
INNER JOIN dbo.CONTENT_Event
ON View_CMS_Tree_Joined_Versi ons.Docume ntForeignK eyValue = dbo.CONTENT_Event.[eventID ]
WHERE dbo.CONTENT_Event.EventDat e >= CAST(FLOOR(CAST( GETDATE() AS FLOAT)) AS DATETIME)
SELECT dbo.CONTENT_Event.eventID,
FROM dbo.View_CMS_Tree_Joined_V
INNER JOIN dbo.CONTENT_Event
ON View_CMS_Tree_Joined_Versi
WHERE dbo.CONTENT_Event.EventDat
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Oh yes, that should not make any difference
so either use either of the query
SELECT dbo.CONTENT_Event.eventID, dbo.CONTENT_Event.EventNam e + ' (' + EventOrgName + ')' + Convert(varchar(10), dbo.CONTENT_Event.EventDat e, 103)
FROM dbo.View_CMS_Tree_Joined_V ersions
INNER JOIN dbo.CONTENT_Event
ON View_CMS_Tree_Joined_Versi ons.Docume ntForeignK eyValue = dbo.CONTENT_Event.[eventID ]
WHERE dbo.CONTENT_Event.EventDat e >= Cast(Convert(varchar(10), GetDate(), 101) as DateTime)
Or
SELECT dbo.CONTENT_Event.eventID, dbo.CONTENT_Event.EventNam e + ' (' + EventOrgName + ')' + Convert(varchar(10), dbo.CONTENT_Event.EventDat e, 103)
FROM dbo.View_CMS_Tree_Joined_V ersions
INNER JOIN dbo.CONTENT_Event
ON View_CMS_Tree_Joined_Versi ons.Docume ntForeignK eyValue = dbo.CONTENT_Event.[eventID ]
WHERE Cast(Convert(varchar(10), dbo.CONTENT_Event.EventDat e, 103) as DateTime) >= Cast(Convert(varchar(10), GetDate(), 103) as DateTime)
so either use either of the query
SELECT dbo.CONTENT_Event.eventID,
FROM dbo.View_CMS_Tree_Joined_V
INNER JOIN dbo.CONTENT_Event
ON View_CMS_Tree_Joined_Versi
WHERE dbo.CONTENT_Event.EventDat
Or
SELECT dbo.CONTENT_Event.eventID,
FROM dbo.View_CMS_Tree_Joined_V
INNER JOIN dbo.CONTENT_Event
ON View_CMS_Tree_Joined_Versi
WHERE Cast(Convert(varchar(10), dbo.CONTENT_Event.EventDat
ASKER
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.
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.
FROM dbo.View_CMS_Tree_Joined_V
INNER JOIN dbo.CONTENT_Event
ON View_CMS_Tree_Joined_Versi
WHERE dbo.CONTENT_Event.EventDat