Converting date to custom format

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

SQL Server 2000 Forums

SQL Server Development (2000)

Converting date to custom format

Author

Topic

sanctos
Starting Member

19 Posts

Posted - 2005-08-24 : 15:54:08

Hello all,

I have a date field in a database that is in the 'mm/dd/yyyy' format. I want to create a view with an alias field, converting the date into a string that has the date formatted in 'mm/dd/yy' WITHOUT the leading zeroes. (Ex...2/3/05)

Using CONVERT(varchar, [My Date Field], 1) doesn't work.

Is there a way to do this?

Sanctos

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-08-24 : 16:12:09

There is no convert style that will do that exact format so if you need to do this in sql (rather than a presentation layer which should be the case) you'll need to do something like this:

select	convert(varchar,datepart(month, dt)) 
	+ '/' + convert(varchar,datepart(day, dt)) + '/' 
	+ right(datename(year, dt),2) as dateCustomFormat
from	(select getdate() dt) a

Be One with the Optimizer
TG

sanctos
Starting Member

19 Posts

Posted - 2005-08-24 : 16:22:19

Thank you very much. Before I tried the following:


CONVERT (varchar, DATEPART(m, [Date])) + '/' + CONVERT (varchar, DATEPART(d, [Date])) + '/' + CONVERT (varchar, DATEPART(yy, [Date]))

...and it was still giving me a 4-digit year. After applying the RIGHT function, it worked. Any idea why the 'yy' didn't work?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-08-24 : 16:34:06

yeah, "yy" is the same as "year". They both specify the 4 digit year portion of a datetime.

Be One with the Optimizer
TG

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-25 : 01:10:57

Where do you want to show dates with this format?
If possible use format function in your presentation layer

Otherwise no need to use convert function
This will also work

Select left(month(getdate()),2) +'/'+ left(day(getdate()),2)+'/'+
right(year(getdate()),2)

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources