|
 |
asp_databases thread: Find birthdays in a single SQL statement
Message #1 by "Doron Meiraz" <doronm@z...> on Wed, 16 Aug 2000 10:37:59 +0300
|
|
Hi everyone,
I have a small database (in Access) which I wrote web front to it.
I'd like to display all people who were born in a specific month. (no matter
which year or which date).
I'm experiencing a problem: Is there a way, in a single SQL query, to get a
list of all these users?
I've tried something like: select * from users where birthdaydate
#01/??/??#
("birthdaydate" is a date type field)
I thought of a way of getting the list all users, than using VBScipt
commands display only the ones with certain month ... but I'm sure there's
much simpler solution.
Can anyone help me on this?
Thanks,
Doron.
Message #2 by "Ken Schaefer" <ken@a...> on Wed, 16 Aug 2000 18:37:57 +1000
|
|
SELECT field1, field2, field3
FROM table1
WHERE Month(field1) = 1
(will find all records where the birth month is January)
Don't use SELECT *
http://www.adOpenStatic.com/faq/selectstarisbad.asp
Cheers
Ken
----- Original Message -----
From: "Doron Meiraz"
To: "ASP Databases" <asp_databases@p...>
Sent: Wednesday, August 16, 2000 5:37 PM
Subject: [asp_databases] Find birthdays in a single SQL statement
>
> Hi everyone,
> I have a small database (in Access) which I wrote web front to it.
> I'd like to display all people who were born in a specific month. (no
matter
> which year or which date).
> I'm experiencing a problem: Is there a way, in a single SQL query, to get
a
> list of all these users?
> I've tried something like: select * from users where birthdaydate
> #01/??/??#
> ("birthdaydate" is a date type field)
>
> I thought of a way of getting the list all users, than using VBScipt
> commands display only the ones with certain month ... but I'm sure there's
>
> much simpler solution.
>
> Can anyone help me on this?
> Thanks,
> Doron.
Message #3 by "Nick Middleweek" <nickm@t...> on Wed, 16 Aug 2000 11:26:50 +0000
|
|
Once again Ken comes back with the answer!
Ken,
Is that Month command a standard SQL command or is it specific to Access?
Can I use it in SQL Server 7?
I've got a SAMS teach yourself in 21 Days SQL Server 7 and SQL book, both
don't list that command!
I was thinking on the lines of converting the date field to a string and
then striping out what I needed to compare.
Regards
Nick Middleweek
----------
> SELECT field1, field2, field3
> FROM table1
> WHERE Month(field1) = 1
>
> (will find all records where the birth month is January)
Message #4 by "Nick Middleweek" <nickm@t...> on Wed, 16 Aug 2000 11:33:59 +0000
|
|
Ken,
False Alert!
I've just found it. I SQL7 you could use
SELECT field1, field2, field3
FROM table1
WHERE DATEPART(month,field1) = 1
Regards
Nick
----------
> SELECT field1, field2, field3
> FROM table1
> WHERE Month(field1) = 1
>
> (will find all records where the birth month is January)
Message #5 by "Doron Meiraz" <doronm@z...> on Wed, 16 Aug 2000 16:45:23 +0300
|
|
Thanks everyone !
That really helped.
BTW - can someone point me to a good online SQL reference ?
and again - Thanks.
Doron.
> -----Original Message-----
> From: Nick Middleweek
> Sent: Wednesday, August 16, 2000 2:34 PM
> To: ASP Databases
> Subject: [asp_databases] Re: Find birthdays in a single SQL statement
>
>
> Ken,
>
> False Alert!
>
> I've just found it. I SQL7 you could use
>
> SELECT field1, field2, field3
> FROM table1
> WHERE DATEPART(month,field1) = 1
>
>
> Regards
> Nick
>
>
> ----------
>
> > SELECT field1, field2, field3
> > FROM table1
> > WHERE Month(field1) = 1
> >
> > (will find all records where the birth month is January)
Message #6 by "Dana Coffey" <dcoffey@x...> on Wed, 16 Aug 2000 09:27:13 -0400
|
|
This is one of my favorites:
http://w3.one.net/~jhoffman/sqltut.htm
Dana Coffey
Technologist, Xceed, Inc.
112 Krog St. Atlanta, GA 30307
tel. xxx-xxx-xxxx x 5013
dcoffey@x...
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Everything should be as simple as it is, but not simpler.
----Albert Einstein
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
{{-----Original Message-----
{{From: Doron Meiraz
{{Sent: Wednesday, August 16, 2000 9:45 AM
{{To: ASP Databases
{{Cc: 'Ken Schaefer'; 'Nick Middleweek'
{{Subject: [asp_databases] Re: Find birthdays in a single SQL statement
{{
{{
{{Thanks everyone !
{{That really helped.
{{
{{BTW - can someone point me to a good online SQL reference ?
{{
{{and again - Thanks.
{{
{{Doron.
{{
{{> -----Original Message-----
{{> From: Nick Middleweek
{{> Sent: Wednesday, August 16, 2000 2:34 PM
{{> To: ASP Databases
{{> Subject: [asp_databases] Re: Find birthdays in a single SQL statement
{{>
{{>
{{> Ken,
{{>
{{> False Alert!
{{>
{{> I've just found it. I SQL7 you could use
{{>
{{> SELECT field1, field2, field3
{{> FROM table1
{{> WHERE DATEPART(month,field1) = 1
{{>
{{>
{{> Regards
{{> Nick
{{>
{{>
{{> ----------
{{>
{{> > SELECT field1, field2, field3
{{> > FROM table1
{{> > WHERE Month(field1) = 1
{{> >
{{> > (will find all records where the birth month is January)
{{
Message #7 by "Ken Schaefer" <ken@a...> on Thu, 17 Aug 2000 11:39:55 +1000
|
|
> Is that Month command a standard SQL command or is it specific to Access?
> Can I use it in SQL Server 7?
Specific to Access (which the original poster was using).
For SQL Server use DatePart() (which I think also works in Access)
Cheers
Ken
|
|
|
View Cart
