-1

I'm working in SQL Server Management Studio 2008. I have a column called BATCH_DATE which has entries in this format 2007-01-01 00:00:00. I need these to be changed into 2007-01-31 00:00:00 format. Please help.

BhatiaAshish answered my question and i already pressed the green checkmark for him/her. This question is answered.

Tjun
  • 13
  • 4
  • @NEER - That's what he says in the question header! Microsoft invented a command to do that - although I like to take DAY-1 days off, add a month and knock off a day – Cato Sep 22 '16 at 11:15
  • 1
    Possible duplicate of [Get the last day of the month in SQL](http://stackoverflow.com/questions/1051488/get-the-last-day-of-the-month-in-sql) – neer Sep 22 '16 at 11:17

4 Answers4

0

You could add 30 days to each one:

select dateadd(day, 30, batch_date)

If that doesn't work, then clarify what you mean in your question.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Something like is exactly what i'm looking for, except what about the months that only have 30 days or 29? – Tjun Sep 22 '16 at 11:14
0
DECLARE @BATCH_DATE as datetime = '2007-01-01 00:00:00';

SET @BATCH_DATE = DATEADD(day,-1,DATEADD(month,1,@BATCH_DATE));

select @BATCH_DATE
Cato
  • 3,652
  • 9
  • 12
0
Select dateadd(day, -1, dateadd( month, 1, batch_date))
DatabaseCoder
  • 2,004
  • 2
  • 12
  • 22
0
DECLARE @Month Datetime
Set  @month='2007-01-01 00:00:00'
SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@month)+1,0))
Alfaiz Ahmed
  • 1,698
  • 1
  • 11
  • 17