How to get sysdate with various formats?

Question

How to get sysdate in the following format in SQL Server?

1/1/2014

1/2/2014

1/3/2014

ie M/D/YYYY

I searched on google, I have different formats, but I want the following format. Please help me.

Thanks.

Answer 1

See the CONVERT documentation in the msdn article here

Here you can see many formats. For your case use

SELECT CONVERT(NVARCHAR(20),yourdate,101) as formatteddate

According to your editing question, I would recommend datepartrelated functions likeMONTH, DAY, YEAR

SELECT CAST(MONTH(GETDATE()) AS VARCHAR(2))+'/'+CAST(DAY(GETDATE()) AS VARCHAR(2))+'/'+CAST(YEAR(GETDATE()) AS VARCHAR(4))

Other editing 2 years ago

SELECT CAST(MONTH(DATEADD(yy,-2,GETDATE())) AS VARCHAR(2))+'/'+CAST(DAY(DATEADD(yy,-2,GETDATE())) AS VARCHAR(2))+'/'+CAST(YEAR(DATEADD(yy,-2,GETDATE())) AS VARCHAR(4))

Answer 2

Use this code:

SELECT CONVERT(VARCHAR(10), GETDATE(), 101) AS [MM/DD/YYYY]

OR

SELECT CONVERT(VARCHAR(10), GETDATE(), 101) AS [M/D/YYYY]

Answer 3

SELECT FORMAT ( GETDATE(), 'MM/dd/yyyy', 'en-US' ) AS DateConvert;

SELECT FORMAT ( GETDATE(), 'M/d/yyyy', 'en-US' ) AS DateConvert;

SELECT CAST(MONTH(GETDATE()) AS VARCHAR(2))+'/'+CAST(DAY(GETDATE()) AS VARCHAR(2))+'/'+CAST(YEAR(GETDATE())- 2 AS VARCHAR(4))

Fiddle

FORMAT (Transact-SQL)

SELECT LTRIM(STR(MONTH(GETDATE())))+'/'+LTRIM(STR(DAY(GETDATE())))+'/'+STR(YEAR(GETDATE()),4)

Fiddle

DECLARE @dt datetime
SET @dt= GETDATE()
SELECT LTRIM(STR(MONTH(@dt)))+'/'+LTRIM(STR(DAY(@dt)))+'/'+STR(YEAR(@dt),4)

Fiddle Demo

 SELECT STUFF(REPLACE('/'+CONVERT(CHAR(10),@SomeExampleDate,101),'/0','/'),1,1,'')

Fiddle