Is there a convert function that can be used to change the format of a date from 6/27/2005 to 27-Jun-2005 ?

This is what I am using:

select (datepart(dd,dateinsp) )
& '-'& DateName(Month,(datepart(Month,dateinsp)))
& '-'& Datepart(yyyy,dateinsp)
from Date_cleanup

I've tried various methods of using the cast as Varchar and the Convert(varchar(50),dateinsp) but continually get the following error

Syntax error converting the nvarchar value 'January' to a column of data type int.

Thanks all,

SELECT REPLACE(CONVERT(VARCHAR,GETDATE(),106),' ','-')

“I sense many useless updates in you... Useless updates lead to fragmentation... Fragmentation leads to downtime...Downtime leads to suffering..Fragmentation is the path to the darkside.. DBCC INDEXDEFRAG and DBCC DBREINDEX are the force...May the force be with you" -- http://sqlservercode.blogspot.com/

" I sense lack of friends in you" if you think that was a funny statement think again. Not all programmers created the mess they are in. In most cases it's a matter of cleaning up the mess of others before them. If you don't have anything positive to add then keep your comments to yourself. If you would have read my post thoroughly you would have seen that your recommendation was pretty much useless.

"May you drop of the earth"--

Cole,

Problem # 1: & is the bitwise AND operator and can only be used on integers, it is not the concatenation operator which is +

Problem # 2: DatePart returns an int and the second parameter of datename is expecting a DateTime data type or something that is implictly castable to a datetime type. You would be better using DateName and forget DatePart

Problem # 3: You did not tell us whether dateinsp column is varchar or datetime

Problem # 4: SQLMenace did give you a working solution (assuming that dateinsp is of type datetime)

Problem # 5: SQLMenance is apparently a big Star Wars fan and his "useless updates" quote is his way of mocking/honoring/imitating Yoda, and it is his signature, he puts it on every post and not just this one. It is nothing personal.

Problem # 6: You don't attack people that are trying to help you.

Here is your solution:

-- How to take this 6/27/2005 and make it 27-Jun-2005

CREATE TABLE Date_Cleanup
    (dcID int IDENTITY(1,1)
    ,dtDateInsp DATETIME
    ,vchDateInsp varchar(50)
)

INSERT Date_Cleanup (dtDateInsp, vchDateInsp) VALUES ('1/4/05','1/4/05')
INSERT Date_Cleanup (dtDateInsp, vchDateInsp) VALUES ('2/4/2005','2/4/2005')
INSERT Date_Cleanup (dtDateInsp, vchDateInsp) VALUES ('12/4/05','12/4/05')
INSERT Date_Cleanup (dtDateInsp, vchDateInsp) VALUES ('11/24/05','11/24/05')
INSERT Date_Cleanup (dtDateInsp, vchDateInsp) VALUES ('10/24/2005','10/24/2005')

-- Cole's code reworked
select DATENAME(dd,dtDateInsp)
    + '-'+ DateName(Month,dtDateInsp)
     + '-'+ DateName(yyyy,dtDateInsp)
,DATENAME(dd,vchDateInsp)
    + '-'+ DateName(Month,vchDateInsp)
     + '-'+ DateName(yyyy,vchDateInsp)
from Date_cleanup

-- SQLMenace Example -- expanded
SELECT REPLACE(CONVERT(VARCHAR,dtDateInsp ,106),' ','-')
, REPLACE(CONVERT(VARCHAR,CAST(vchDateInsp as DATETIME),106),' ','-')
FROM Date_cleanup

David Lundell
Principal Consultant and Trainer
www.mutuallybeneficial.com

Thanks David, your time and your advice is much appreciated.

Best Regards,
C