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

Posted by Amritha444 under Sql Server on 8/14/2013 | Points: 10 | Views : 5728 | Status : [Member] | Replies : 10
Hi all

i have a table
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
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




Responses

Posted by: Bandi on: 8/14/2013 [Member] [MVP] Platinum | Points: 50

Up
0
Down

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

Posted by: Bandi on: 8/14/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
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

Posted by: Amritha444 on: 8/16/2013 [Member] Starter | Points: 25

Up
0
Down
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

Posted by: Bandi on: 8/16/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
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

Posted by: Amritha444 on: 8/16/2013 [Member] Starter | Points: 25

Up
0
Down
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

Posted by: Amritha444 on: 8/16/2013 [Member] Starter | Points: 25

Up
0
Down
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

Posted by: Bandi on: 8/16/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
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

Posted by: Amritha444 on: 8/16/2013 [Member] Starter | Points: 25

Up
0
Down
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

Posted by: Bandi on: 8/16/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
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

Posted by: Bandi on: 8/31/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
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

Login to post response