Problem inserting integers and date in a sql server 2005 datatable row and selecting it af

Hi,

I have soma ado.net code that inserts 7 parameters in a database ( a date, 6 integers).

I also use a self incrementing ID but the date is set as primary key because for each series of 6 numbers of a certain date there may only be 1 entry. Moreover only 1 entry of 6 integers is possible for 2 days of the week, (tue and fr).

I manage to insert a row of data in the database, where the date is set as smalldatetime and displays as follows: 1/05/2007 0:00:00 in the table.

I want to retrieve the series of numbers for a certain date that has been entered (without taking in account the hours and seconds).

A where clause seems to be needed but I don't know the syntax or don't find the right function

I use the following code to insert the row :

command.Parameters.Add(new SqlParameter("@.Date", SqlDbType.DateTime, 40, "LDate"));

command.Parameters[6].Value = DateTime.Today.ToString();

command.ExecuteNonQuery();

and the following code to get the row back (to put in arraylist):

"SELECT C1, C2, C3, C4, C5, C6 FROM Series WHERE (LDate = Today())"

WHERE LDate = '" + DateTime.Today.ToString() + "'"

Which is the correct syntax? Is there a better way to insert and select based on the date?

I don't get any error messages and the code executes fine but I only get an empty datatable in my dataset (the table isn't looped for rows I noticed while debugging).

Today's date is in the database but isn't found by my tsql code I think.

Any help would be greatly appreciated!

Grtz

Pascal

Instead of

command.Parameters.Add(new SqlParameter("@.Date", SqlDbType.DateTime, 40, "LDate"));

command.Parameters[6].Value = DateTime.Today.ToString();

command.ExecuteNonQuery();
use

command.Parameters.Add(new SqlParameter("@.Date", SqlDbType.DateTime));

However you could use GetDate() (server local time) or GetUtcDate() (server time in GMT)
The select could be

"SELECT C1, C2, C3, C4, C5, C6 FROM Series WHERE LDate DATEADD(day, -1, GetDate()) ANDBETWEEN GETDATE()"

will give you a 24hr windows of records.

|||

thx Tatworth for your quick reply

Tested it quickly but getting this sqlexception: "An expression of non-boolean type specified in a context where a condition is expected, near 'DATEADD' "

Don't have the time to investigate what the select code exactly does and how it works but will do tomorrow and keep you informed!

|||Should have been

"SELECT C1, C2, C3, C4, C5, C6 FROM Series WHERE LDateBETWEEN DATEADD(day, -1, GetDate()) ANDGETDATE()"

The between go in the wrong place!

|||

YES! It worked... THANKS

BUT: do not understand why this needs to be put like this and we can't simply use the GetDate() function

When I follow the logic of the functions and comparison one would think it will return the day of yesterday?

|||

>BUT: do not understand why this needs to be put like this and we can't simply use the GetDate() function

Let me run getDate on my Server
SELECT GetDate(), DATEADD(day, -1, GETDATE())

2007-05-05 09:59:11.280 2007-05-04 09:59:11.280

It returns not just the date but the time as well, thus the BETWEEN will select all records with date/time from 09:59 yesterday to 09:59 today.