Pass date to function return YYYYMM

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Pass date to function return YYYYMM

Author

Topic

nerdygirl61
Starting Member

21 Posts

Posted - 2012-09-12 : 10:49:48

I need to convert a date to a YearMonth in a function. In my attempt below it only returns the first line, it doesn't seem to be concatinating the YYYY to the MM. What am I doing wrong?

/**
select dbo.ufnYearMonth ('9/1/2012')
**/

ALTER FUNCTION ufnYearMonth
( @bxDateOfService Date )
RETURNS nvarchar (6)
AS
BEGIN
DECLARE @Result nvarchar (6)
SELECT @Result =
convert(char,(datepart(yyyy,@bxDateOfService))) +
convert(char,(datepart(mm,@bxDateOfService)))
RETURN @Result
END
GO

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-12 : 10:53:43

You can simplify it using a style that matches your requirment - in this case:

CONVERT(CHAR(6), @bxDateOfService, 112)

Also, unless there is a compelling reason to create a function to do this, just do it in-line. That would be more efficient and simpler.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-09-12 : 10:54:33

[code]CONVERT(char(6), @bxDateOfService, 112)[/code]You're better off using that expression directly rather than wrapping it in a UDF, it will perform much faster.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-12 : 10:55:36

why do you need a function for this?

its just a matter of doing it inline as

DATENAME(yyyy,yourdatefield) + CAST(DATEPART(mm,yourdatefield) AS varchar(2))

also if possible its highly recommended to do this at front end especially if you're planning to do further date manipulations with this value

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

nerdygirl61
Starting Member

21 Posts

Posted - 2012-09-12 : 11:15:17

Thank you so much. Being self taught (with your help) I am learning when to use a function or not. I am pulling services of one type, unless the client has a service of another type in the same month. I am processing a years worth of data so I need to use the YYYYMM to do the comparison.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-12 : 11:21:31

quote:
Originally posted by nerdygirl61

Thank you so much. Being self taught (with your help) I am learning when to use a function or not. I am pulling services of one type, unless the client has a service of another type in the same month. I am processing a years worth of data so I need to use the YYYYMM to do the comparison.

if you want to have comparison of current against last year month or current vs previous month you should be maintaining them as integer rather than string as it will anyway undergo implicit conversion when you do the number based comparison

ie

YEAR(datevalue)*100 + MONTH(datevalue)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

nerdygirl61
Starting Member

21 Posts

Posted - 2012-09-12 : 18:53:47

There will be times I will need to use it this way, thank you!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-12 : 22:22:09

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources