Query a datetime in SQL Server 2005 Express

Question

How to query the rows based on DateTime in SQL Server 2005 Express installed on windows server 2008 r2?

I have code that selects rows based on from and to date values. It's working in my Windows 7 system . But, It's not working on Windows Server 2008 R2 ... Any help

this.filterreportTableAdapter.FillBy(this.cRdataset.filterreport_datatable, fromdate, todate);

and my SQL query is

SELECT * 
FROM tablename 
WHERE DATE BETWEEN @fromdate and @todate

What datatype is your `DATE` column? (that's a **really bad** column name, btw - since it's a data type in version 2008 and onwards). What **values** are you passing in for `@fromdate` and `@todate` — marc_s, Jul 22 '12 at 07:52

score 1 · Accepted Answer · answered Jul 22 '12 at 08:17

It sounds like date/time values are being passed as strings. This is always a bad idea, and formatting issues abound. The best approach here is simply: use correctly-typed parameters. if both client and server know it is a DateTime, then it is passed as a primitive vaue, not a string - thus no formatting problems.

Make sure that fromdate and todate in the c# are DateTime, and that @fromdate, @todate and tablename.DATE in the TSQL are datetime.

score 0 · Answer 2 · answered Jul 22 '12 at 08:01

0

Please set the datetime format same as the one on your windows 7 e.g. : dd/MM/yyyy

answered Jul 22 '12 at 08:01

HatSoft

11,077
3
28
43

score 0 · Answer 3 · answered Jul 22 '12 at 08:02

0

it seems that you have a date format mistake when passing date parameters. there are two solutions: first, you can try to use date format that works everywhere that is: YYYYMMDD ex : 20120722. another solution is to make a DateDiff in sql query Best regard

answered Jul 22 '12 at 08:02

Hassan Boutougha

3,871
1
17
17

1

Or an *actual* solution... Use typed parameters, which aren't affected by formatting issues. – Marc Gravell Jul 22 '12 at 08:13

Query a datetime in SQL Server 2005 Express

3 Answers3