Query for SQL Date in C#

Question

Let's say I have a table with a Date column. In my C# application, I want to be able to query for rows using the LIKE operator on the DateTime column. My problem is, how do I handle the regional settings?

For example, if the current regional settings are set to de-DE, the date format of windows is dd.mm.yyyy. Now the user might enter something like %2009, resulting in the following query:

select * from MyTable where to_char(MyDateColumn, 'dd.MM.yyyy') like '%2009'

But with different regional settings, the input of the user will look different of course, so to_char doesn't work anymore.

How can I work around this? Oh, and I'm looking for a database-independent solution.

Many thanks in advance!

Answer 1

Don't convert it into textual form!!!

Use DATEPART to check this: http://msdn.microsoft.com/en-us/library/aa258265(SQL.80).aspx

eg:

select * from MyTable where DATEPART(year, MyDateColumn) = 2009

Answer 2

Don't compare dates with strings. Instead, use something like the DATEPART function to compare to just the year.

Answer 3

You don't want to use a LIKE operator on a DATETIME column, believe me.

select * from MyTable where year(MyDateColumn) = 2009

or

select * from MyTable where datepart(yy, MyDateColumn) = 2009

Answer 4

And specially, do use parametrised queries to prevent SQL Injection attacks.

Answer 5

Display date in dd/mm/yy format

SELECT [Sales_id] ,[sales_no] ,[sequence_id] ,[sales_Date], CONVERT(char(12), sales_Date, 3) as sales_Date1 ,[Net_Total] ,[Roundoff] FROM [Furniture].[dbo].[SalesMaster]

288 109 1 2010-08-21 00:00:00.000 21/08/10 1040.00

291 110 1 2010-08-21 00:00:00.000 21/08/10 103.00

294 111 1 2010-08-21 00:00:00.000 21/08/10 7128.00 -0.40

Please Refer http://msdn.microsoft.com/en-us/library/aa172602(SQL.80).aspx