0

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!

Improve this question

5 Answers 5

Reset to default
6

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
Improve this answer
Sign up to request clarification or add additional context in comments.

14 Comments

"I" don't want to use a LIKE operator on a DateTime column, but a customer is requesting this feature... The year and datepart functions only work on MS SQL Server, right?
The customer doesn't want to use the LIKE operator either, I'm quite sure. What exactly are they requesting?
Well, if there's a DateTime box on a form, they want to be able to query with placeholders, like %2009 to get all rows for 2009, for example.
What should happen if they enter "%12009", or %1209%"? (Don't say that this can't happen.)
It should happen what you would expect from LIKE ;) "%12009" -> date values with months ending with 1 in the year 2009. "%1209%" -> ah... i guess that would be 12th of september in any year. I think you get the point ;)
|
2

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
Improve this answer

1 Comment

But the datepart function does only work on MS SQL Server, right? I'm looking for a database-independent solution...
2

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

Improve this answer

2 Comments

Yes, I'm doing that. The select I wrote was just as an example. But you have a point ;)
Great. Believe me, making such comments is a good thing because a lot of people isn't aware of that problem.
1

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

Improve this answer

Comments

0

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

Improve this answer

2 Comments

But the datepart function does only work on MS SQL Server, right? I'm looking for a database-independent solution...
I don't know whether DATEPART is T-SQL only. Same with the YEAR function.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.