date & time extraction

• Delario

  SSC Eights!

  Points: 826

  More actions

  January 9, 2012 at 7:01 am

  #254703

  hi

  is there a way to extract just the month from a particular column in a table

  I have this but not really sure

  select to_char(<my column name>, 'mm') from <my table name>

  New kid on the block

• Cadavre

  SSC-Forever

  Points: 41690

  More actions

  January 9, 2012 at 7:07 am

  #1430439

  BEGIN TRAN

  --1,000,000 Random(-ish) rows of data

  SELECT TOP 1000000 IDENTITY(INT,1,1) AS ID,

  CONVERT(DATE,RAND(CHECKSUM(NEWID())) * 30000 + CAST('1945' AS DATETIME)) AS randomDate

  INTO #testEnvironment

  FROM master.dbo.syscolumns sc1, master.dbo.syscolumns sc2, master.dbo.syscolumns sc3

  --Query the table

  SELECT TOP 5

  ID, randomDate, MONTH(randomDate) AS monthNumber,

  DATENAME(MONTH,randomDate) AS [monthName]

  FROM #testEnvironment

  ROLLBACK

  ---

  Forever trying to learn
  My blog - http://www.cadavre.co.uk/
  For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
  For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  ---

• Paul White

  SSC Guru

  Points: 150468

  More actions

  January 9, 2012 at 7:33 am

  #1430456

  Delario (1/9/2012)

  ---

  select to_char(<my column name>, 'mm') from <my table name>

  That looks like Oracle syntax! The SQL Server functions are as Cadavre demonstrates, but just as a curiosity, I understand this code would work unchanged on both SQL Server and Oracle...

  CREATE TABLE dbo.Example (x date)

  INSERT dbo.Example VALUES ('11-jul-11')

  SELECT

  {fn MONTH(e.x)}

  FROM dbo.Example AS e

  Not recommending the ODBC scalar functions; this is just for fun.

  Paul White
  All articles available on SQL.kiwi
  @SQL_Kiwi