Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Convert function 1

Status
Not open for further replies.

nkm

Programmer
May 27, 2001
45
US
Hi

Is there any way by which we can convert date to the format
Mon-yy.
The standard styles provided with convert function do not support this.
I have to do this in a stored procedure.

thanks

 
No you do not have to

Try this my friend


select substring(convert(char(8),getdate(),5),4,8)


To have a handy sp to show you the list of all date options in convert try this (once created run as exec sp__date)


create procedure sp__date (@date datetime = NULL,
@dont_format char(1) = null
)
as
begin
declare @style int
declare @msg varchar(80)

select @style = 0

if @date is NULL
select @date = getdate()

while (@style <= 12)
begin
select @msg = convert(char(4), @style) +
convert(char(30), @date, @style) +
convert(char(5), @style+100) +
convert(char(30), @date, @style+100)

print @msg

select @style = @style + 1
end

end
go



good luck and I hope this helps
 
Hi nkm,

The solution suggested above only returns 'mm-yy' if I am right where as what is desired is 'ddd-yy'. Perhaps the above sql should have been

select substring(convert(char(8),getdate(),10),4,8)

so that we atleast get date in the 'dd-yy' format. But that still does not seem to solve the problem because if I am right the character version of the day of week is sought. I would suggest the following sql as of now. Hope it helps.

select substring(DATENAME(dw,getdate()),1,3) + &quot;-&quot; + substring(DATENAME(yy,getdate()),3,2)

All the best,
Vijay
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top