DateAdd problem

Question

I am trying to subtract 1 month from the current YearMOnth...It's datatype is a varchar(6)

Current    HowIwant
201001     200912

I am using this but not getting the correct result

SELECT yearmonth, dateadd(M, datediff(month, 0, yearmonth) - 1, 0)
from tableA

Answer 1

0

You can't use datetime functions on varchar like that - it has no idea that the string is supposed to represent 'yyyymm'

try this

SELECT 
 left(
  convert(varchar, 
    dateadd(M, datediff(month, 0, convert(date, yearmonth +'01', 112)) - 1, 0)
    ,112)
 ,6)

This converts your string +'01', which is the style 112, to a date, and then does the dateadd manipulation.

answered Mar 01 '12 at 04:18 PM

Kev Riley ♦♦
46k ● 38 ● 43 ● 69

@kev Riley That works but produces the answer in the format 2009-11-01 00:00:00.000

Mar 01 '12 at 04:33 PM sqlLearner 1

sorry missed off the final convert.....edited!

Mar 01 '12 at 04:45 PM Kev Riley ♦♦

(comments are locked)