I am tryin to get records from a table within a month in sql compact edition.
Here is the sql query I know:
DECLARE @startDate as DATETIME, @EndDate as DATETIME
@startDate = GetDate();
@ENdDate = DATEADD(m,1,@startDate)
select * from table where (columnname between @startdate and @enddate)
I know that you have to send one script at a time, but how can you declare variables in sql ce(I guess it doesn't accept declare)?
I think my answer is very late for this question, but hope it will be useful for someone.
You can't declare some variable in SQL CE, because just one statement can be used per command. As ErikEJ said in this link.
You need to refactor your script to one big statement, if it's possible!
I will be very happy to hear a better solution.
Take a look at post How do I populate a SQL Server Compact database?
and see if tool referenced there may help you.
If you call it through an application (I'm not sure how you read the data)
Prepare your query just like this:
select * from table where (columnname between ? and ?)
but I'm not sure if you can use the between keyword. may be you need to change this.
then you need to add your SqlCeParameter objects like this:
cmd.Parameters.Add(new SqlCeParameter("p1", SqlDbType.DateTime, myDate));
I'm not familiar with SQL-CE, but I think you're missing some Set
statements. Try this:
DECLARE @startDate as DATETIME, @EndDate as DATETIME
Set @startDate = GetDate();
Set @ENdDate = DATEADD(m,1,@startDate)
select * from table where (columnname between @startdate and @enddate)
Update
See the Using Parameters in Queries in SQL CE reference from MSDN. You are correct in that Declare is not a valid keyword, so you'll need to the query as a parameterized version from the application itself.
select * from table where (columnname between ? and ?)