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

SET DATEFORMAT DMY

DECLARE @EndDate DATE = '20130813', @StartDate DATE = '20130815'

select * 

from table_a 

where @StartDate IS NULL 

     OR @EndDate IS NULL 

    OR ( CAST( date AS DATE) BETWEEN @startDate AND @EndDate )

Note: make sure to convert both operands to same format for date comparisons..
Important note is that do not use incorrect data types for DATE type of data.. For storing Dates into database use DATE/DATETIME/DATETIME2 data types...

-- Sample Script to check Date comparison

SET DATEFORMAT DMY

-- Table Creation

CREATE TABLE TestDates (date VARCHAR(15), id int identity)

--Insert sample data

insert into testDates(date) values('13/08/2013'),('10/08/2013'),('11/08/2013'),('12/08/2013'),('14/08/2013'),('15/08/2013')

-- actual data in table

SELECT * FROM TestDates



--Query with date comparison

DECLARE @EndDate DATE = '20130815', @StartDate DATE = '20130813'

SELECT *

FROM testDates 

where @StartDate IS NULL 

	OR @EndDate IS NULL 

	OR CAST(date AS DATE) BETWEEN @startDate AND @EndDate 

GO

-- To drop the table

DROP TABLE TestDates

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Amritha444, if this helps please login to Mark As Answer. | Alert Moderator

Refer this link for Date comparisons in SQL server
http://visakhm.blogspot.in/2012/12/different-ways-to-implement-date-range.html

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Amritha444, if this helps please login to Mark As Answer. | Alert Moderator

thanks for your reply

your example is working well when i applied this to my case error arising as
Conversion failed when converting date and/or time from character string.

Amritha444, if this helps please login to Mark As Answer. | Alert Moderator

Can you post some sample data of your table's Date column ?

SELECT CAST(date AS DATE) FROM table_a

If the above query gives error, you have some incorrect data stored in the table...

SELECT CAST(date AS DATE) FROM table_a WHERE ISDATE(Date) = 1

Let me know whether the above query gives any error for your data?
http://technet.microsoft.com/en-us/library/ms187347.aspx

Finally, Do you have data in DATE Column as DD/MM/YYYY or MM/DD/YYYY ?

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Amritha444, if this helps please login to Mark As Answer. | Alert Moderator

here is my code
declare @StartingDate varchar(200)
declare @EndDate varchar(200)
set @StartingDate='02/02/2013'
set @EndDate='02/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 .that columns contains null values also will it make any issue?

Amritha444, if this helps please login to Mark As Answer. | Alert Moderator

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, if this helps please login to Mark As Answer. | Alert Moderator

why are you passing start & End dates in MM/DD/YYYY format.. Is it compulsory to pass in that format?
And post me the first value in the column ProgressDate

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Amritha444, if this helps please login to Mark As Answer. | Alert Moderator

i changed to
set @StartingDate='20130215'
set @EndDate='20130816'
but same error
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

Amritha444, if this helps please login to Mark As Answer. | Alert Moderator

highlighted are the modifications....

declare @StartDate DATE

declare @EndDate DATE

set @StartDate= '20130202'  -- YYYYMMDD format

set @EndDate='20130225'  

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 CAST(MG_AssignedActivity.ProgressDate AS DATE)  BETWEEN @StartingDate AND @EndDate 

NOTE: Remember that do not store DATE type data as strings in database .... Use PROPER datatypes...
otherwise we will get this type of problem while querying and degrading performance too...
1) We must use CAST function in WHERE condition in your case, this will cause performance issue for large set of data
2) if you forgot to cast the ProgressDate as DATE then you will get incorrect results....

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Amritha444, if this helps please login to Mark As Answer. | Alert Moderator

can you mark it as answer if it helps u

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Amritha444, if this helps please login to Mark As Answer. | Alert Moderator