Filter Date problem - 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)

Filter Date problem

Author

Topic

Ayreona
Starting Member

1 Post

Posted - 2011-01-10 : 05:30:01

Hi
I have a table Giftlist with a column DateMade of the type varchar(8). An example of an entry in the column DateMade: 10122010
that stands for 10 December 2010. This is an project for school and I'm not allowed to change the type of the column to Date or something like that.
I have to select the entries where the DateMade lies between two dates which are chosen through a DateTimePicker on a Windows Form in VB.NET. I don't really know how I have to build the sql statement to get what I want.

Any suggestions anyone?

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2011-01-10 : 06:08:03

One workaround is to use stuff function and place hyphen "-" after 2nd and 4th character.
It will become 10-12-2010. Then you can convert it to datetime 105 format.

Example
Select Convert(datetime,'10-12-2010',105)

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2011-01-10 : 06:14:51

Something like this:

Declare @Test table
(Srno int,
SDate varchar(8)
)

Insert into @Test
Select 1,'11082010' union
Select 2,'10092010' union
Select 3,'05072010'

Select * From @Test where Convert(Datetime,Stuff(Stuff(SDate,3,0,'-'),6,0,'-'),105)
between '2010-08-10' and '2010-09-10'

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-01-12 : 05:54:06

quote:
Originally posted by pk_bohra

Something like this:

Declare @Test table
(Srno int,
SDate varchar(8)
)

Insert into @Test
Select 1,'11082010' union
Select 2,'10092010' union
Select 3,'05072010'

Select * From @Test where Convert(Datetime,Stuff(Stuff(SDate,3,0,'-'),6,0,'-'),105)
between '2010-08-10' and '2010-09-10'

Use unambigious date format
http://beyondrelational.com/blogs/madhivanan/archive/2010/06/03/understanding-datetime-column-part-ii.aspx

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources