date column in sql server and querying

Question

3.00/5 (1 vote)

See more:

I am generating a report from the UI which involves performing a query like where currrentdate between date1 and date2.

The application is being hosted for an indian company. The database by default stores it in mm/dd/yy.

But the customer prefers to see the format dd/mm/yy when he enters the dates in the UI and doesnt care about the locale in the sql server.

By default its set to US, which is fine..but how to do work with it to query accurately?.
Can someone help me here to help set up the database column with proper locale and guide me through the sql query.

I am asking the sql date column is by default- mm/dd/yy.

The where clause of the query should be like-

dd-mm-yy format?
WHERE currentDate between '1-1-2013' AND '31-12-2014'
OR
mm/dd/yy format?.
WHERE currentDate between '1/1/2013' AND '12/31/2014'
OR

Posted 17-Jul-14 16:57pm

Member 10562713

Add a Solution

Comments

Per Söderlund 18-Jul-14 0:29am

Isn´t this handled by ADO.net if you use SqlParameters with your queries?

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

ArunRajendra · Accepted Answer · 2014-07-17T18:10:00

Solution 1

Try this. It doesenot use any seperator. This also helps in avoiding mm/dd and dd/mm erros.

112 format is yyyymmdd.

WHERE convert(currentDate,112) between '20130101' AND '20141231'

Posted 17-Jul-14 18:10pm

ArunRajendra

v2

Bhavik_Patel · Accepted Answer · 2014-07-17T18:53:00

Solution 2

Here is your solution

SQL

where convert(varchar(20),currentDate ,103) between '1/1/2013' AND '12/31/2014'

You have to convert your date to dd/mm/yyyy format, 103 gives dd/mm/yyyy format

for more datetime format command you can refer Convert function in sql[^]

Posted 17-Jul-14 18:53pm

Bhavik_Patel

PrakashCs.net · Accepted Answer · 2014-07-17T19:29:00

Solution 3

use this solution

SQL

WHERE GETDATE() BETWEEN CONVERT(DATE,'01/01/2014') AND CONVERT(DATE,'12/31/2014')

Posted 17-Jul-14 19:29pm

PrakashCs.net