MDYY SQL Format

Question

Is there a way to format a sql date like this:

3/11/2009 (as opposed to03/11/2009)

20/7/2008 (as opposed to 20/07/2008)

Thanks!

Answer 1

In T-SQL itself, you can look in Books Online about the CONVERT keyword. It shows a list of codes that give different date formats.

Answer 2

SET DATEFORMAT mdy

See the set dateformat docs : http://technet.microsoft.com/en-us/library/ms189491.aspx

Answer 3

Try this UDF..

create function dbo.mdyDate(@theDate DATETIME,@fmt INT) 
returns VARCHAR(10) 
as 
BEGIN
    declare   @ans   VARCHAR(10)
    set @ans = replace(convert(varchar(10),@theDate,@OptFmt),'/0','/') 
    if left(@ans,1)='0' set @ans=substring(@ans,2,9)
    return @ans
END

Examples

select dbo.mdyDate( dateadd(d,-10,getDate()),103 )   -- Nov 4, 2009
select dbo.mdyDate( dateadd(m,-5,getDate()),103 )    -- June 14, 2009
select dbo.mdyDate( dateadd(d,-10,getDate()),101 )   -- Nov 4, 2009
select dbo.mdyDate( dateadd(m,-5,getDate()),101 )    -- June 14, 2009