Help with retrieving Dates in a table

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)

Help with retrieving Dates in a table

Author

Topic

gmunky
Starting Member

5 Posts

Posted - 2007-03-27 : 10:59:00

I'm having problem writing a query that retrieves the dates
(mm-dd-yy) that matches both the month and the year that a user inputs.

for instance:

date table
01-31-05
02-23-05
01-22-05
03-25-05

I want to be able to retrieve these dates:

01-31-05
01-23-05

i tried using LIKE approach:

select from table where date like '%05'
but this query retrieves all the '05 dates.

is there a way to use LIKE for both the month and the year?

Thanks for any help!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-27 : 11:01:13

select * from [date] where datepart(month, col1) = 5 and datepart(year, col1) = 7

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-27 : 11:01:37

Do not use VARCHAR to store dates.

Peter Larsson
Helsingborg, Sweden

X002548
Not Just a Number

15586 Posts

Posted - 2007-03-27 : 11:03:46

quote:
Originally posted by Peso

select * from [date] where datepart(month, col1) = 5 and datepart(year, col1) = 7

Peter Larsson
Helsingborg, Sweden

7?

SELECT DATEPART(year,Getdate())

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-27 : 11:05:58

quote:
Originally posted by gmunky

I'm having problem writing a query that retrieves the dates
(mm-dd-yy) that matches both the month and the year that a user inputs.

Peter Larsson
Helsingborg, Sweden

X002548
Not Just a Number

15586 Posts

Posted - 2007-03-27 : 11:55:06

datepart(year, col1) = 7

will never be true

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-27 : 12:03:20

I see now. I just put 7 for year to not confuse with 5 as I used for month, forgot about 2007!

The record with date 20070513 will be fetched when using
select * from [date] where datepart(month, col1) = 5 and datepart(year, col1) = 2007

But since OP intends to only use the last two digits
select * from [date] where datepart(month, col1) = 5 and datepart(year, col1) % 100 = 7

Thanks!

Peter Larsson
Helsingborg, Sweden

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-04-04 : 06:06:01

quote:
Originally posted by gmunky

I'm having problem writing a query that retrieves the dates
(mm-dd-yy) that matches both the month and the year that a user inputs.

for instance:

date table
01-31-05
02-23-05
01-22-05
03-25-05

I want to be able to retrieve these dates:

01-31-05
01-23-05

i tried using LIKE approach:

select from table where date like '%05'
but this query retrieves all the '05 dates.

is there a way to use LIKE for both the month and the year?

Thanks for any help!

Why dont you make your life easy by storing dates in proper DATETIME datatype?

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources