SQL Statement - part dates selection

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

General SQL Server Forums

New to SQL Server Programming

SQL Statement - part dates selection

Author

Topic

AlexMann
Starting Member

4 Posts

Posted - 2006-06-20 : 08:34:19

Hi all,

I have a database with a field which contains a load of dates in this format 01/03/1998

What I want to do is select all the records from the database where the month of the date is what ever, this month or the month the customer selected etc.

I suspected it may be something like the following (which doesnt work)

sql = "SELECT * FROM avail_lowermill WHERE CH_Arrival.month = " & showmonth & " ORDER by CH_Arrival"

Can anyone suggest how I can do this?

Thanks in advance

nr
SQLTeam MVY

12543 Posts

Posted - 2006-06-20 : 08:41:13

select * from avail_lowermill WHERE datediff(mm,convert(datetime,CH_Arrival.month,103),getdate()) = 0

Check the convert styles for the date - you don't say whether it is dd/mm/yyyy or the strange mm/dd/yyyy which I believe some people use.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-06-20 : 08:54:18

What is the datatype of the date column? If you used varchar, then change it to DATETIME

Madhivanan

Failing to plan is Planning to fail

AlexMann
Starting Member

4 Posts

Posted - 2006-06-20 : 09:13:11

Ah, that returns the error:

Undefined function 'convert' in expression.

I guess becuase I was a fool and forgot to mention that im working with an Access database at the moment.

Is there an access equiv that can be used?

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-06-20 : 10:26:56

quote:
Is there an access equiv that can be used?

Yes. It's here:

http://www.sqlteam.com/forums/forum.asp?FORUM_ID=3

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

AlexMann
Starting Member

4 Posts

Posted - 2006-06-20 : 10:50:58

SELECT Count(PAYMENTS.ID) AS CountOfID FROM PAYMENTS WHERE received >=#02/11/2006# and received<=#02/25/2006#

Is this the code you were refering to?

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-06-20 : 11:00:26

Nope. Mine was a reference to the access forum (the access equiv of this sql server forum). You should post your question there instead...

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-20 : 12:49:44

quote:
Originally posted by AlexMann

Ah, that returns the error:

Undefined function 'convert' in expression.

I guess becuase I was a fool and forgot to mention that im working with an Access database at the moment.

Is there an access equiv that can be used?

Yes.

sql = "SELECT * FROM avail_lowermill WHERE MONTH(CH_Arrival) = " & showmonth & " AND YEAR(CH_Arrival) = "& showyear & " ORDER by CH_Arrival"

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources