DATEPART AND CAST QUESTION

[kaijanm]

Hello. TIA!

I have this as a selector in a sproc and I don't think the result I'm getting is correct.

'BA' +CAST(datepart(yy, GETDATE()) AS VARCHAR) + CAST(DATEPART(MM, GETDATE()) AS VARCHAR) AS 'BatchNumber'

What I expect to get here is BA0409. What I get instead is BA20049.

What is going on?
Please help. I'm frustrated with it.

That and why do I have to explicitly do the cast here? If I don't, I get errors about it.

Kimberly

 

[SQLSister]

you have to cast because you are concatenating values together. Otherwise it doesn't know what they are.

Not sure why you aren;t getting just the two-digit year, you could try:

Right(CAST(datepart(yy, GETDATE()) AS VARCHAR),2) + CAST(DATEPART(MM, GETDATE()) AS VARCHAR) AS 'BatchNumber'

Questions about posting. See faq183-874

 

[SQLBill]

You are getting a four digit year because that's what 'yy' returns. Even the examples in BOL show that returns a four digit year. Microsoft does some strange things sometimes.

-SQLBill