Month(datetimefield) instead of Month()
Buggy example, but you get the idea. ;)
----------------------------
James Ang
Senior Programmer
MedSeek, Inc.
[EMAIL PROTECTED]
-----Original Message-----
From: James Ang
Sent: Tuesday, July 30, 2002 4:13 PM
To: '[EMAIL PROTECTED]'
Subject: RE: SQL Question: Selecting and grouping by date
If you don't need to perform aggregation:
SELECT Year(datetimefield) as YY, Month() as MM, Day(datetimefield)
as DD, *
FROM yourtable
ORDER BY YY, MM, DD
If you need to perform aggregation based on date (and not date AND
time):
SELECT Year(datetimefield) as YY, Month() as MM, Day(datetimefield)
as DD,
Count(1) AS RecordCountForThatDay
FROM yourtable
GROUP BY YY, MM, DD
ORDER BY YY, MM, DD
Hope this helps.
----------------------------
James Ang
Senior Programmer
MedSeek, Inc.
[EMAIL PROTECTED]
-----Original Message-----
From: Brook Davies [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, July 30, 2002 4:24 PM
To: CF-Talk
Subject: RE: SQL Question: Selecting and grouping by date
Thanks for the quick help, I tried the datepart function but it doesn't
look like it can return more the the date, month OR year. I need to get
all
parts or there may be some inconsistencies in the data.
I also tried the Ben's suggestion using Concatenation.
SELECT Month(myDate) + '/' + Day(myDate) + '/' + Year(myDate)
But I get an error : Syntax error converting the varchar value '/' to a
column of data type int.
At 04:01 PM 30/07/02 -0700, you wrote:
>Have you tried using the DATEPART() Transact-SQL function?
>
>SELECT ID, FirstName, LastName, DateCreated
>FROM Contact
>WHERE DATEPART("m",DateCreated) = 7
>
>The above query will return all Contacts create in the month of July
>(for any year).
>
>Here are the details for the DATEPART() function:
>http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlre
f
>/ts_da-db_2mic.asp
>
>Hope this helps :)
>
>----
>SCOTT VAN VLIET
>BRD.WRKS INTERACTIVE
>T: 714.469.6805
>E: [EMAIL PROTECTED]
>
>
>-----Original Message-----
>From: Brook Davies [mailto:[EMAIL PROTECTED]]
>Sent: Tuesday, July 30, 2002 4:05 PM
>To: CF-Talk
>Subject: SQL Question: Selecting and grouping by date
>
>Is there a way to select the datepart(mydate,"mm/dd/yy") from MSSQL? I
>can
>only seem to return the Day, month or year, but not all three. The
field
>
>contains date & time data and I am trying to group on the date part
but
>
>the time is throwing off the results.
>
>Brook
>
>
>
>
______________________________________________________________________
Signup for the Fusion Authority news alert and keep up with the latest news in
ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
