CODEWITHSUNDEEP
sqldatabase
Christian Hubmann
Christian Hubmann

Reputation: 1512

Query for SQL Date in C#

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!

Upvotes: 0

Views: 1272

Answers (5)

B.Jeevanandam
B.Jeevanandam

Reputation: 11

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 to http://msdn.microsoft.com/en-us/library/aa172602(SQL.80).aspx

Upvotes: 1

Miha Markic
Miha Markic

Reputation: 3240

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

Upvotes: 2

Tomalak
Tomalak

Reputation: 338118

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

Upvotes: 6

John Saunders
John Saunders

Reputation: 161773

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

Upvotes: 0

Tommi Forsström
Tommi Forsström

Reputation: 1467

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

Upvotes: 2

Related Questions