SQL - How to calculate the last day of the month/? RSS

• • Reply
  

  urir10

  Member

  93 Points

  225 Posts

  SQL - How to calculate the last day of the month/?

  Nov 19, 2012 06:26 PM|LINK

  Hi All

  Im really hoping i can get some help with this.

  I currently have a table with a column TimeID which holds the year and month as YYYYMM (i.e 201209 , for September 2012).

  My task is to convert this number to YYYYMMDD with DD being the last day of whatever month it is; so for example 201209 becomes 20120930 or 201210 becomes 20121031.

  Any help will be much appreciated.

  Thanks

  • Edited by urir10 on Nov 19, 2012 06:26 PM
  • Edited by urir10 on Nov 19, 2012 06:26 PM
• • Reply
  

  spapim

  Contributor

  2450 Points

  370 Posts

  Re: SQL - How to calculate the last day of the month/?

  Nov 19, 2012 06:37 PM|LINK

  Hi,

  Try something like this:

  declare @YearMonth varchar(10);
  
  set @YearMonth = '201209';
  
  select DATEADD(DAY, -1, DATEADD(MONTH, 1, CONVERT(DATE, @YearMonth + '01', 112)))

  Hope this helps.

  www.imoveisemexposicao.com.br/imoveis-venda-são_paulo-residencial
  www.imobiliariasemsuzano.com.br
  • Marked as answer by Chen Yu - MSFT on Nov 21, 2012 04:20 AM
• • Reply
  

  limno

  All-Star

  117326 Points

  8003 Posts

  Moderator

  MVP

  Re: SQL - How to calculate the last day of the month/?

  Nov 19, 2012 06:40 PM|LINK

  declare @strdt varchar(6)='200909'
  
  select CONVERT(varchar(10),dateadd(day,-1,dateadd(month,1,cast(@strdt+'01'as datetime))),112)
  
  --SQL Server 2012
  Select CONVERT(varchar(10),EOMONTH(cast(@strdt+'01'as datetime)),112)

  Limno
  
  Format your SQL query with instant sql formatter:
  http://www.dpriver.com/pp/sqlformat.htm
  • Marked as answer by urir10 on Nov 19, 2012 09:04 PM
• • Reply
  

  urir10

  Member

  93 Points

  225 Posts

  Re: SQL - How to calculate the last day of the month/?

  Nov 19, 2012 09:05 PM|LINK

  Thank You both!!! it worked great!