SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
Home Tags Articles Editorials Forums Scripts Videos Blogs QotD Books Ask SSC SQL Jobs Training Authors About us Contact us Newsletters Advertise Write for us


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » SQL Server 7,2000 » T-SQL » Date/Time group

Date/Time group Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
TSQLExplorer
Posted Thursday, November 19, 2009 10:49 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, March 09, 2010 10:03 AM
Points: 55, Visits: 36
I need to show record counts counts for each month of the year... January 160 records, February, 900 records based on records which contain a datetime field containing short date data (example 4/17/2006)

Is there a method to convert the exact date to just show the Month the date occured in for the purposes of the counting I need to do?

The format of the date field is datetime 8 characters

Thanks!
Post #821805
steveb.
Posted Thursday, November 19, 2009 10:53 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 8:29 AM
Points: 1,644, Visits: 4,240
you can use MONTH

SELECT MONTH(GETDATE())

or for the Month name 

SELECT DATENAME(MM,GETDATE())
Post #821809
TSQLExplorer
Posted Thursday, November 19, 2009 11:04 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, March 09, 2010 10:03 AM
Points: 55, Visits: 36
If the field name in this case is "referdate", where does it go with your code?

referdate (MM,GETDATE()) ?

my example of course which did not work.. thus my question..

Thanks..
Post #821818
Lynn Pettis
Posted Thursday, November 19, 2009 11:59 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 5:48 PM
Points: 11,922, Visits: 12,023
Is the data restricted to a 12 month period or is there more than one year?

Also, referdate would go in place of getdate().


Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #821858
TSQLExplorer
Posted Monday, November 23, 2009 8:38 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, March 09, 2010 10:03 AM
Points: 55, Visits: 36
The data spans two calendar years (one school year)...

September one year to June the next year
Post #823298
TSQLExplorer
Posted Monday, November 23, 2009 8:41 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, March 09, 2010 10:03 AM
Points: 55, Visits: 36
If my data changes calendar year (which it does, as I'm measuring data throughout a school year... September one year to June of the next year) will need to show the month number and the year in the returned data. Are there similar formatting codes to show just the current year (09, or 2009), or just the month number (01 January, 04 April), etc?
Post #823303
steveb.
Posted Monday, November 23, 2009 8:51 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 8:29 AM
Points: 1,644, Visits: 4,240
Three are formats to strip out any part of the date by using DATEPART
for the year you would use

 SELECT DATEPART(yy,GETDATE())

to build your own custom date formats you can use the multiple functions 

 SELECT DATEPART(day, GETDATE()),datename(month, GETDATE()), DATEPART(year, GETDATE())


A lot more info here

http://msdn.microsoft.com/en-us/library/ms174420.aspx
Post #823312
Lynn Pettis
Posted Monday, November 23, 2009 9:04 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 5:48 PM
Points: 11,922, Visits: 12,023
There is this function, YEAR().

SELECT YEAR(GETDATE())


Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #823321
TSQLExplorer
Posted Monday, November 23, 2009 10:47 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, March 09, 2010 10:03 AM
Points: 55, Visits: 36
Thanks for the fishing lesson and rod!
Post #823394
« Prev Topic | Next Topic »


Permissions Expand / Collapse

 
 
Copyright © 2002-2010 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use