Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

datetime to month - year Expand / Collapse
Author
Message
Posted Thursday, March 19, 2009 10:14 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, July 29, 2011 7:49 AM
Points: 107, Visits: 626
How convert to do Datetime to month-year

input -
2009-01-23 1:12:52.253

output needed-
Jan-2009?

I tried by datepart but not got requried results.
Post #679616
Posted Thursday, March 19, 2009 10:20 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Saturday, March 15, 2014 4:31 AM
Points: 2,808, Visits: 7,173
try..

SELECT DATENAME(mm,'2009-01-23 1:12:52.253') + '-' + datename(yy,'2009-01-23 1:12:52.253')

Post #679626
Posted Thursday, March 19, 2009 10:22 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 8:55 PM
Points: 22,492, Visits: 30,199
Will this work for you?
select left(datename(mm, getdate()), 3) + '-' + cast(datename(yyyy, getdate()) as char(4))




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 #679630
Posted Thursday, March 19, 2009 10:55 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, August 19, 2013 11:46 PM
Points: 111, Visits: 609
The question I would ask is where are you displaying the output. Is it in Excel, reporting services? I gather you want to get sales per month or something similar for this. My query would look something like this.
SELECT DATEADD( MONTH, DATEDIFF( MONTH, 0, '11-Jan-2009'), 0)

Then the aggregation on that, and in the report I would format it to be MMM-yyyy.
My opinion
Post #680131
Posted Thursday, March 19, 2009 11:30 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 11:47 PM
Points: 35,959, Visits: 30,252
Lynn Pettis (3/19/2009)
Will this work for you?
select left(datename(mm, getdate()), 3) + '-' + cast(datename(yyyy, getdate()) as char(4))



No casting needed when you used DATENAME, Lynn.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #680139
Posted Wednesday, December 15, 2010 8:59 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, December 23, 2011 11:38 AM
Points: 15, Visits: 36
Jeff -
How then would one be able to sort chronologically on this field? Is there a way to keep the format while changing the data type to a numeric format?
Post #1035236
Posted Thursday, December 16, 2010 7:10 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 11:47 PM
Points: 35,959, Visits: 30,252
andell_ramsay (12/15/2010)
Jeff -
How then would one be able to sort chronologically on this field? Is there a way to keep the format while changing the data type to a numeric format?


To answer the questions above in order... You can't and yes.

See the following SQL Spackle article for one way to do this...
http://www.sqlservercentral.com/articles/T-SQL/71511/


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1035816
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse