using datepart to combine dates

[thorny00]

I have a date/time field that contains the date in yyyy-mm-dd hh:mm:ss format. I'm trying to just strip off the year and month and combine into a new field.
example:
DATEPART(MONTH,dbo.SERVICE.YMDEFF)+ DATEPART(YEAR,dbo.SERVICE.YMDEFF) AS NEWEFF

but this is summing the month and year
month is 07 and year is 2002 the result is 2009

I need 200207

All help is greatly appreciated!!

 

[JamesLean]

SELECT CONVERT(varchar(6), ymdeff, 112)

--James

 

[MaffewW]

this gives you almost what you want (the month part is missing the zero on jan to sept)

select convert(varchar,DATEPART(year,dbo.SERVICE.YMDEFF))+ convert(varchar,DATEPART(month,dbo.SERVICE.YMDEFF)) AS NEWEFF

will try and get the exact format you want, though if poss this would probably better be done at the client end

 

[MaffewW]

Looks like James has beaten me with a better way! (Did try that way but couldnt find the right date value)

 

[thorny00]

James,

This works great!! Another question, what happens if I have two different date fields that I'm trying to do this to??

example
dbo.SERVICE.YMDEFF, convert (varchar(6), NEWEFF,112),
dbo.SERVICE.YMDPAID, convert (varchar(6), NEWPAID,112),

I'm getting an 'invalid column name' for neweff and newpaid

Thanks!!

 

[JamesLean]

I'm not sure I understand. If I'm right you have a table with two datetime columns and you want to display each column just as yyyymm format? If so then this should work:

SELECT CONVERT(varchar(6), DateCol1, 112), CONVERT(varchar(6), DateCol2, 112)
FROM Table1

If not, can you explain exactly what result you want?

--James