Query to search by date. - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Query to search by date.

Author

Topic

satish.gorijala
Posting Yak Master

182 Posts

Posted - 2011-02-10 : 01:30:25

Hi, i have a screen with search box where user can enter date manually(not datepicker). He may enter date in the format dd/mm/yyyy.

I need to retrive data from database table based on that date. i writeen a query but it not retriving the records.
I tried this, but its not retriving the records. Can any one suggest the correct query.

SELECT * FROM [dbo].[table] Where CONVERT(VARCHAR(10),Duedate,103) = '10/2/2011'

developer :)

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-02-10 : 02:16:03

Check this

SELECT * FROM [dbo].[table] Where CONVERT(VARCHAR(10),Duedate,103) = '10/02/2011'

satish.gorijala
Posting Yak Master

182 Posts

Posted - 2011-02-10 : 02:37:05

This is the same query what i tried and the same i posted. but its not giving any result

quote:
Originally posted by MIK_2008

Check this

SELECT * FROM [dbo].[table] Where CONVERT(VARCHAR(10),Duedate,103) = '10/02/2011'

developer :)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-02-10 : 02:43:57

What datatype is column DueDate? DATETIME or VARCHAR?

N 56°04'39.26"
E 12°55'05.63"

satish.gorijala
Posting Yak Master

182 Posts

Posted - 2011-02-10 : 03:24:58

its Datetime datatype

quote:
Originally posted by Peso

What datatype is column DueDate? DATETIME or VARCHAR?

N 56°04'39.26"
E 12°55'05.63"

developer :)

Ranjit.ileni
Posting Yak Master

183 Posts

Posted - 2011-02-10 : 03:42:33

check dateformat

--Ranjit

satish.gorijala
Posting Yak Master

182 Posts

Posted - 2011-02-10 : 03:46:21

I didn't get what you mean ? Can you give query for my requirement with your thought(dateformat)

quote:
Originally posted by Ranjit.ileni

check dateformat

--Ranjit

developer :)

Ranjit.ileni
Posting Yak Master

183 Posts

Posted - 2011-02-10 : 04:02:42

I am not sure , But this may useful


--==============
SET DATEFORMAT dmy
go
CREATE TABLE TableA
(
Duedate datetime
)
go
INSERT INTO TableA VALUES('10/02/2011')
go

SELECT CONVERT(VARCHAR(10),Duedate,103)FROM TableA  --10/02/2011
go
DROP TABLE TableA

--================
SET DATEFORMAT mdy
go
CREATE TABLE TableB
(
Duedate datetime
)
go
INSERT INTO TableB VALUES('10/02/2011')
go
SELECT CONVERT(VARCHAR(10),Duedate,103)FROM TableB  --02/10/2011
go
DROP TABLE TableA

--Ranjit

satish.gorijala
Posting Yak Master

182 Posts

Posted - 2011-02-10 : 04:19:46

Here i am not inserting date value to the column. I am taking the userinput directly to the query.

quote:

Originally posted by Ranjit.ileni

I am not sure , But this may useful


--==============
SET DATEFORMAT dmy
go
CREATE TABLE TableA
(
Duedate datetime
)
go
INSERT INTO TableA VALUES('10/02/2011')
go

SELECT CONVERT(VARCHAR(10),Duedate,103)FROM TableA  --10/02/2011
go
DROP TABLE TableA

--================
SET DATEFORMAT mdy
go
CREATE TABLE TableB
(
Duedate datetime
)
go
INSERT INTO TableB VALUES('10/02/2011')
go
SELECT CONVERT(VARCHAR(10),Duedate,103)FROM TableB  --02/10/2011
go
DROP TABLE TableA

--Ranjit

developer :)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-02-10 : 04:30:59

set dateformat mdy
SELECT * FROM [dbo].[table] Where Duedate>= @input_date and Duedate< dateadd(day,1,@input_date )

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-02-10 : 04:32:53

If the time portion of DueDate column always is 00:00:00.000, you can use this (see, no conversion needed)

SELECT * FROM dbo.Table1 WHERE DueDate = '20110210' -- Always use ISO dateformat for accuracy

If your DueDate column has time information, use an open-ended search solution as this (You can always use this even if there is "no" timeinformation, because time is always 00:00:00.000 as default).

SELECT * FROM dbo.Table1 WHERE DueDate >= '20110210' AND DueDate < '20110211' -- Always use ISO dateformat for accuracy

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources