how to get month or year from a given date

• hemanth T

  SSC Eights!

  Points: 807

  More actions

  December 9, 2010 at 9:21 am

  #244073

  hi all,

  i have a date

  '3-12-2010' or '3-dec-2010'

  i need the year --

  i need the month-- from the given date --with conversions if needed and i need month number--int

  Thanks in advance.

• Lowell

  SSC Guru

  Points: 323518

  More actions

  December 9, 2010 at 9:39 am

  #1260639

  As long as you are working with real date or datetime variables, SQL has a suite of built in functions to get the pieces you are after:

  /*--results

  MO YR MO2 WD

  12 2010 December Thursday

  */

  select

  month(getdate()) As MO,

  year(getdate()) As YR,

  datename(month,getdate()) As MO2,

  datename(weekday,getdate()) AS WD

  now if your data is stored as varchars, you need to convert them back to dates, and then use the functions against them;

  /*--Results

  MO YR MO2 WD

  12 2010 December Friday

  */

  select

  month(CONVERT(datetime,'3-dec-2010')) As MO,

  year(CONVERT(datetime,'3-dec-2010')) As YR,

  datename(month,CONVERT(datetime,'3-dec-2010')) As MO2,

  datename(weekday,CONVERT(datetime,'3-dec-2010')) AS WD

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!