Need Date Search query - 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 2008 Forums

Transact-SQL (2008)

Need Date Search query

Author

Topic

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2013-01-24 : 06:57:26

I have table like this

SalesDate ReceiptNo Drinks Food
Jan-01 00000344-00000380 1025.00 8297.90
Jan-02 00000381-00000409 452.30 5372.85
Jan-03 00000410-00000419 193.50 1768.21
Jan-04 00000420-00000448 1119.50 4762.81
Jan-05 00000449-00000478 559.00 4460.11
Jan-06 00000479-00000500 195.80 2855.58
Jan-07 00000501-00000517 452.90 2450.25
Jan-08 00000518-00000527 1006.00 1816.88
Jan-09 00000528-00000546 241.00 2538.05
Jan-10 00000547-00000559 705.00 2367.10

and my expectation is to select date between jan-01 to jan -05
(date field is in varchar datatype)

sqlbay
Starting Member

12 Posts

Posted - 2013-01-24 : 07:28:46

In jan-01, 01 is date or year?

SQL Server Professional http://sqlbay.blogspot.in

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2013-01-24 : 07:57:32

Im sorry I didn't mention that.

Jan-01 (mmm-dd) format

sqlbay
Starting Member

12 Posts

Posted - 2013-01-24 : 08:17:44

SELECT *
FROM Table
WHERE CONVERT(int,right(SalesDate,2)) between CONVERT(int,right('jan-01',2)) and CONVERT(int,right('jan-05',2))

SQL Server Professional http://sqlbay.blogspot.in

djj55
Constraint Violating Yak Guru

352 Posts

Posted - 2013-01-24 : 09:00:11

If you will need to go over months, say Jan-26 to Feb-02 then something like:

select * from table where CAST(REPLACE(SalesDate, '-', ' ') + ', 2013' AS DATE) between date1 and date2;

This is just to give you an idea. Hope it helps.

djj

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources