how to compare date in dd/mm/yyyy format with mm/dd/yyyy in sqlserver

Question

0.00/5 (No votes)

See more:

Hi all

i have a table

SQL

table_a
fields
id  int
name varchar
date varchar

here saving date in dd/mm/yyyy format
i want to fetch data from this table between two dates

SQL

select * from table_a  where ((Date >= @StartingDate ) or (@StartingDate is null)) 
and ((Date <= @endDate ) or (@endDate is null))

but didnt returned any row as date saved in dd/mm/yyyy fromat how to clear this issue

thanks and regards
Amritha

Posted 13-Aug-13 23:34pm

amritha444

Updated 13-Aug-13 23:38pm

ridoy

v3

Add a Solution

Comments

Bernhard Hiller 14-Aug-13 5:37am

date varchar
That's wrong design. Do it correctly, and thus you'll get rid of the problems!

Maciej Los 14-Aug-13 5:56am

Why a date filed with date data type values is varchar?

amritha444 14-Aug-13 5:56am

doing modification in a project . lot of data in that table.i know this is wrong design but somebody else done so . i just want to make a report . how to do this

1 solution

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-08-14T00:29:00

Solution 1

Try to convert[^]:

SQL

SELECT *
FROM table_a
WHERE CONVERT(DATETIME, [date],112) BETWEEN @startDate AND @endDate

And please, read this: sum of row value date wise[^] to know what bad table design can cause.

[EDIT]

amritha444 - 17 mins ago wrote:

here is some of column values
24/5/2013
24/5/2013
2/7/2013
2/7/2013
25/6/2013
25/6/2013
6/6/2013
27/5/2013

OK, try add SET DATEFORMAT instruction:

SQL

SET DATEFORMAT dmy;
 
DECLARE @tmp TABLE (dateval VARCHAR(30))
 
INSERT INTO @tmp (dateval)
SELECT '24/5/2013'
UNION ALL SELECT '24/5/2013'
UNION ALL SELECT '2/7/2013'
UNION ALL SELECT '2/7/2013'
UNION ALL SELECT '25/6/2013'
UNION ALL SELECT '25/6/2013'
UNION ALL SELECT '6/6/2013'
UNION ALL SELECT '27/5/2013'
UNION ALL SELECT '27/5/2013'
UNION ALL SELECT NULL
UNION ALL SELECT '7/13/2113'
UNION ALL SELECT '12/12/2215'


SELECT dateval, CONVERT(INT, LEFT(dateval, CHARINDEX('/',dateval)-1)) AS [d],
		CONVERT(INT, SUBSTRING(dateval, CHARINDEX('/',dateval)+1,CHARINDEX('/',dateval, CHARINDEX('/',dateval)+1) - CHARINDEX('/',dateval)-1)) AS [m],
		CONVERT(INT, RIGHT(dateval,4)) AS [y]
FROM @tmp
WHERE CHARINDEX('/',dateval)>0

Result:

24/5/2013   24  5   2013
24/5/2013   24  5   2013
2/7/2013    2   7   2013
2/7/2013    2   7   2013
25/6/2013   25  6   2013
25/6/2013   25  6   2013
6/6/2013    6   6   2013
27/5/2013   27  5   2013
27/5/2013   27  5   2013
7/13/2113   7   13  2113
12/12/2215  12  12  2215

[/EDIT]

Posted 14-Aug-13 0:29am

Maciej Los

Updated 16-Aug-13 1:58am

v4

Comments

Joezer BH 14-Aug-13 6:47am

5ed!

Maciej Los 14-Aug-13 6:53am

Thank you ;)

amritha444 16-Aug-13 2:19am

thanks for the reply

error message showing as
Conversion failed when converting date and/or time from character string

Maciej Los 16-Aug-13 3:24am

Example data, please...

amritha444 16-Aug-13 5:03am

my code
declare @StartingDate datetime
declare @EndDate datetime
set @StartingDate='02/02/2013'
set @EndDate='09/25/2013'

SELECT MG_Activity.ActivityId,CASE MG_Activity.FolderName WHEN 'Empty' THEN ' ' ELSE MG_Activity.FolderName END AS FolderName, MG_Activity.ActivityName,
MG_ActivityParent.ParentId, MG_Activity.ActivityDate, MG_Activity.Description AS Note, MG_Activity.AdminId, MG_Activity.Status,
MG_Activity.IsActive, MG_Activity.Priority, MG_AssignedActivity.UserId, MG_Users.FirstName AS UserName, MG_AssignedActivity.AssignId,
MG_AssignedActivity.Progress, REPLACE(CONVERT(varchar, MG_AssignedActivity.Duration, 103), '-', '/') AS DueDate, MG_AssignedActivity.StartingDate,
MG_AssignedActivity.ProgressDate, CASE MG_ActivityDate.SubTask WHEN 0 THEN 'Create Sub Task' ELSE 'Sub Task' END AS SubTask

FROM MG_Meeting_Group INNER JOIN
MG_Meeting_ActionItem ON MG_Meeting_Group.GroupID = MG_Meeting_ActionItem.GroupID INNER JOIN
MG_Meeting_SubGroup ON MG_Meeting_ActionItem.SubGroupID = MG_Meeting_SubGroup.SubGroupID RIGHT OUTER JOIN
MG_Activity INNER JOIN
MG_AssignedActivity ON MG_Activity.ActivityId = MG_AssignedActivity.ActivityId INNER JOIN
MG_Users ON MG_AssignedActivity.AdminId = MG_Users.UserID INNER JOIN
MG_ActivityParent ON MG_Activity.ActivityId = MG_ActivityParent.ActivityId INNER JOIN
MG_ActivityDate ON MG_Activity.ActivityId = MG_ActivityDate.ActivityId ON
MG_Meeting_ActionItem.ActivityID = MG_Activity.ActivityId LEFT OUTER JOIN
MG_Meeting_Meeting ON MG_Meeting_ActionItem.MeetingID = MG_Meeting_Meeting.MeetingID

where @StartingDate is null or @EndDate is null OR CONVERT(DATETIME,MG_AssignedActivity.ProgressDate,112) BETWEEN @StartingDate AND @EndDate
data in ProgressDate field is in dd/mm/yyyy format .but that columns contains null values also will it make any issue?

Maciej Los 16-Aug-13 5:34am

Data, not sql query...

amritha444 16-Aug-13 5:12am

when i tried to run
SELECT CAST(ProgressDate AS DATE) FROM MG_AssignedActivity
same error shows
Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.

amritha444 16-Aug-13 5:37am

here is some of column values
24/5/2013
24/5/2013
2/7/2013
2/7/2013
25/6/2013
25/6/2013
6/6/2013
27/5/2013

Maciej Los 16-Aug-13 5:59am

See my answer now (EDIT part).

amritha444 16-Aug-13 6:18am

only error changed
Msg 242, Level 16, State 3, Line 6
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

Maciej Los 16-Aug-13 7:57am

Of course, taht's because your table contains a data like this:
SELECT CONVERT(DATETIME, '7/13/2113')
How to catch above error? See my answer.

amritha444 16-Aug-13 8:21am

Great effort Maciej Los
thanks a million

Maciej Los 16-Aug-13 8:24am

You're very welcome ;)
Call again!

amritha444 16-Aug-13 8:28am

of course :)