Update field to date format mmm yy

I use the following code in an sp:

----------------------------------------------------------
CREATE PROCEDURE sp_ReturnMailProcessing

@myDonorID int
AS

update tblDonors
set MailStatus = 'Jun 02'
where DonorID = @myDonorID and (MailStatus = 'Yes' or MailStatus = 'YES')
GO
----------------------------------------------------------

I want to set the MailStatus to a text value of the current date in the format mmm yy. MailStatus is of type varchar.

Can you show me the code please? Thanks.
naqayyaAsked:
Who is Participating?
 
nigelrivettConnect With a Mentor Commented:
set MailStatus = right(convert(varchar(9),getdate(),13),6)
0
 
curtis591Commented:
I am not sure how your table is setup but if it a datetime format of sometype you save the data in the with day inside of it and when you want to display the data you do something like to remove the day. Or you could turn your field type into a character to save it but you won't be able to date queries with it then.
select convert(char(4),getdate(),107) + right(datename(yy,getdate()),2)
0
 
curtis591Commented:
you could also use the year function

select convert(char(4),getdate(),0) + right(year(getdate()),2)
0
 
woregulCommented:
set MailStatus = substring(datename(m, getdate()), 1, 3) + ' ' + convert(varchar, datepart(yy, getdate()))
0
 
naqayyaAuthor Commented:
I think nigelrivett's solution is the simplest - but it needed a slight modification:

set MailStatus = right(convert(varchar(9),getdate(),6),6)

The date style 13 or 113 will always return century(yyyy). However 6 works fine.

Thanks all.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.