Date Format Without Zeros - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Date Format Without Zeros

Author

Topic

Johnph
Posting Yak Master

103 Posts

Posted - 2013-05-07 : 15:11:31

I have a query that converts datetime into varchar which looks like this:

SELECT CONVERT(varchar(32),DATE ,101)FROM TABLE

The output gives results that look like this:
04/01/2010
05/01/2012
12/12/2012

Is there a way I can make my output like this:
4/1/10
5/1/12
12/12/12

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-05-07 : 15:25:45

[code]DECLARE @Date AS DATE = SYSDATETIME();
SELECT FORMAT(@Date, 'd/M/yy', 'en-US')[/code]

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-05-07 : 15:27:03

I don't think there is a built-in style to do that (see here: http://msdn.microsoft.com/en-us/library/ms187928.aspx )

You can try to append the parts together as in

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

Johnph
Posting Yak Master

103 Posts

Posted - 2013-05-07 : 15:29:05

It is giving my this error:

'FORMAT' is not a recognized built-in function name.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-05-07 : 15:29:42

quote:
Originally posted by James K

I don't think there is a built-in style to do that (see here: http://msdn.microsoft.com/en-us/library/ms187928.aspx )

You can try to append the parts together as in
SELECT CAST(MONTH(DATE) AS varchar(2)) + '/'
+ CAST(DAY(DATE) AS varchar(2)) + '/'
+ RIGHT(CAST(YEAR(DATE) AS VARCHAR(4)), 2)

Small update.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2013-05-07 : 15:29:53

This piece of stupidity will get rid of the 0 that you wnat gotten rid of.

declare @date varchar(10) = '12/12/2012'
select convert(VARCHAR(2),convert(INT,parsename(replace(convert(VARCHAR(10),convert(DATE,@date),10) ,'-','.'),3)))
+'/' +convert(VARCHAR(2),convert(INT,parsename(replace(convert(VARCHAR(10),convert(DATE,@date),10) ,'-','.'),2)))
+'/' + parsename(replace(convert(VARCHAR(10),convert(DATE,@date),10) ,'-','.'),1)

Jim

Everyday I learn something that somebody else already knew

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-05-07 : 15:30:41

quote:
Originally posted by Johnph

It is giving my this error:

'FORMAT' is not a recognized built-in function name.

Ahh, you must not be using SQL 2012. It's good to include the version of SQL you are working with so we can provide suitable answers for you.

Johnph
Posting Yak Master

103 Posts

Posted - 2013-05-07 : 15:38:53

I ended up using James K's code.

Thank you jimf, lamprey and james for all the help.

You guys are great!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources