Sql Command Problem

Question

0.00/5 (No votes)

See more:

I have a table named accounts and under this table, entryDate is a column which data type is datetime. I need a SELECT command that can select all data of a particular date (like '12-02-2013' ). I use this But not work.

SQL

SELECT * FROM [accounts] WHERE entryDate='12-02-2013'

Thanks in advance, your help is greatly appreciated.

Posted 15-Dec-13 0:28am

sazzad37

Add a Solution

5 solutions

Solution 1

In SQL database you cannot store and retrieve date values directly as dd/MM/yyyy, because in database the datetime will store as yyyy-MM-dd HH:mm:ss,

So my suggestion for you to use convert function in your sql query.

SQL

SELECT * FROM [accounts] where entryDate=convert(varchar(10),'12-02-2013',103)

(or)

SELECT * FROM [accounts] where entryDate=convert(datetime,'12-02-2013',103)

I hope your doubt is cleared, feel free to ask any doubts if you have.

Posted 15-Dec-13 0:37am

M.Abdul Rahman

Comments

sazzad37 15-Dec-13 6:57am

In your command what is 103?

M.Abdul Rahman 15-Dec-13 7:19am

this is a code to get date format in (dd/MM/yyyy)
follow this link you'll get to know all

http://www.w3schools.com/sql/func_convert.asp

Solution 3

Further to the solutions above, SQL Server actually stores datetime types in a set, 8 byte format, regardless of the locale settings. Full details on in SQL Books on Line, but this article covers most of the relevant points[^]

A company I worked for used to continuously get problems with dates in where clauses as it was multi-national. We adopted the use of unambiguous formats to overcome the issues ... e.g. '12-FEB-2013' or '02-DEC-2013' depending on which one you meant.

We also had issues where our datetime literally was a datetime - i.e. included hours, minutes, seconds etc so things like WHERE entryDate='12-FEB-2013' would fail anyway. We got around that particular problem something like this ...

SQL

WHERE dateadd(dd, datediff(dd, 0, entryDate), 0) = '12-FEB-2013'

We got the formatting stuff from this blog on sqlservercentral.com[^]

Posted 15-Dec-13 8:09am

CHill60

Solution 5

hey bro just check database field date and then write same date in you code

Posted 15-Dec-13 18:58pm

Gaurav Makwana

Solution 4

check your system date format....and give the same format in your query...

Posted 15-Dec-13 18:06pm

An@nd Rajan10

Comments

An@nd Rajan10 16-Dec-13 1:20am

hey...why your down voting ,what is the problem ???

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)

Maciej Los · Accepted Answer · 2013-12-15T02:17:00

"But not work" - is not informative at all!

Depending on locale settings[^] SQL Server can store date data types in different formats:
- dd-mm-yyy
- MM/dd/yyy
- mm-dd-yyy
- etc.

To change it use SET DATEFORMAT[^] command, for example:

SQL

SET DATEFORMAT dmy;
SELECT * FROM [accounts] WHERE entryDate='12-02-2013'