SQL query to get date whatever the format it is inserted in database?

Question

0.00/5 (No votes)

See more:

Hii,,

I am using custom format to store the date in the database .It should be stored in this form MM/dd/yyyy. I have to retrieve the results based on the date.My table looks like this:

Id Name Date

1 Diya 09/10/2012

2 Naitik 09/10/2012

Now to retrieve this data if I write a query to select the records on 9/10/2012 then it is showing null results ..What I want is whatever the date format I give(9/10/2012 or 09/10/2012) I should be able to retrieve the results .How can I write the SQL query for this?

Thanks,,,

Posted 10-Sep-12 1:11am

cutie1

Add a Solution

Comments

_Amy 10-Sep-12 7:17am

Currently which query you are using? Can I see that?

VIPR@T 10-Sep-12 7:21am

show your query..

cutie1 10-Sep-12 7:23am

create proc GetDetails @Date Datetime as select * from Details where Date=@Date.This is my stored procedure

2 solutions

Solution 2

If you have custom date format and your query looks like:

SQL

SELECT *
FROM Details
WHERE Date=@Date

use SET DATEFORMAT[^] command before SELECT statement.

Example:

SQL

SET DATEFORMAT mdy;
SELECT *
FROM Details
WHERE Date=@Date

Posted 10-Sep-12 10:49am

Maciej Los

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)

__TR__ · Accepted Answer · 2012-09-10T01:21:00

Solution 1

Assuming you are using SQL Server, you can use DATEDIFF[^] function to compare your dates.
In your where clause you can write it as

SQL

DATEDIFF(DAY, Date, @Date) = 0

Where @Date is the date you are comparing with the date column in your table.

With this approach you don't have to worry about the format in which the date is stored in database.

Posted 10-Sep-12 1:21am

__TR__

Comments

cutie1 10-Sep-12 7:40am

Thanks I got it..

_Amy 10-Sep-12 7:45am

5'ed! :)

__TR__ 10-Sep-12 7:50am

Thanks Amit.

Maciej Los 10-Sep-12 16:49pm

Interesting trick ;)
+5!

__TR__ 11-Sep-12 2:55am

Thanks losmac :)