Dates are essentially formatted numbers. The integer portion represents the number of days since sometime long ago.
Haha.. Thank you for the smart answer; however, the issue does not get resolved since date is still not seperated from datetime. I see casting to an integer would work but would require additional steps to trim the date portion. I was wondering if there
is a better way such as casting to date as available in some other DBs?
If you want, you can convert the date portion of a DateTime to Nvarchar in SQL CE (e.g. CONVERT(NVARCHAR(10), StartDate, 101) or
display only the date portion in C#, but nothing more.
If you want to select all the records with date equal or future to today, my query accomplish this goal (at least on my system).
Your query does the same thing as without casting. The future dates are shown but it does not include today's events. I'm trying to show events >= today.
-------- EDIT ----------
Well I'll be a monkey's uncle - using the integer approach does work to include today:
"SELECT TOP 10 * FROM Events WHERE CAST(StartDate as Integer) >= CAST(getDate() AS Integer) ORDER BY StartDate";
however, the issue does not get resolved since date is still not seperated from datetime. I see casting to an integer would work but would require additional steps to trim the date portion. I was wondering if there is a better way such as casting to date
as available in some other DBs?
For this thread, you have already chosen your db. I think casting the datetime as an integer is the same as casting to a date. Why is it that you think otherwise?
For this thread, you have already chosen your db. I think casting the datetime as an integer is the same as casting to a date. Why is it that you think otherwise?
I was under the impression that casting to an interger wouldn't seperate date from datetime - that 1/19/2013 12:00:00 AM would just be 1192013120000 instead of 1192013 as I wanted. I haven't actually displayed the cast to integer on the page to see what
it looks like but as I said is my last post - it works! Thanks for your feedback.
Dan Bracuk
Contributor
3970 Points
1096 Posts
Re: Limit the rows returned in sql query
Dec 30, 2012 11:53 PM|LINK
Dates are essentially formatted numbers. The integer portion represents the number of days since sometime long ago.
davidsa
Member
210 Points
126 Posts
Re: Limit the rows returned in sql query
Dec 31, 2012 07:18 AM|LINK
Haha.. Thank you for the smart answer; however, the issue does not get resolved since date is still not seperated from datetime. I see casting to an integer would work but would require additional steps to trim the date portion. I was wondering if there is a better way such as casting to date as available in some other DBs?
prabu.raveen...
Contributor
5020 Points
955 Posts
Re: Limit the rows returned in sql query
Dec 31, 2012 08:07 AM|LINK
Hi,
Try the below query,
SELECT Top 5 * FROM Events WHERE cast(StartDate as Date) >= cast(GetDate() as Date)
GmGregori
Contributor
5470 Points
737 Posts
Re: Limit the rows returned in sql query
Dec 31, 2012 03:36 PM|LINK
I don't understand what is your goal.
If you want to select all the records with date equal or future to today, my query accomplish this goal (at least on my system).
Otherwise, if you want to cast to Date a DateTime field in SQL CE you can't because SQL Server Compact 4.0 doesn't support the Date type.
If you want, you can convert the date portion of a DateTime to Nvarchar in SQL CE (e.g. CONVERT(NVARCHAR(10), StartDate, 101) or display only the date portion in C#, but nothing more.
davidsa
Member
210 Points
126 Posts
Re: Limit the rows returned in sql query
Dec 31, 2012 07:44 PM|LINK
Your query does the same thing as without casting. The future dates are shown but it does not include today's events. I'm trying to show events >= today.
-------- EDIT ----------
Well I'll be a monkey's uncle - using the integer approach does work to include today:
Thank you.
Dan Bracuk
Contributor
3970 Points
1096 Posts
Re: Limit the rows returned in sql query
Jan 01, 2013 01:56 AM|LINK
For this thread, you have already chosen your db. I think casting the datetime as an integer is the same as casting to a date. Why is it that you think otherwise?
davidsa
Member
210 Points
126 Posts
Re: Limit the rows returned in sql query
Jan 01, 2013 07:26 AM|LINK
I was under the impression that casting to an interger wouldn't seperate date from datetime - that 1/19/2013 12:00:00 AM would just be 1192013120000 instead of 1192013 as I wanted. I haven't actually displayed the cast to integer on the page to see what it looks like but as I said is my last post - it works! Thanks for your feedback.