Need to format date - 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

SQL Server 2000 Forums

Transact-SQL (2000)

Need to format date

Author

Topic

latingntlman
Yak Posting Veteran

96 Posts

Posted - 2006-12-12 : 16:27:34

I have a datetime field, but I need to show only the month and year, like so mm/yy (i.e. 01/06, 02/06, etc.

Is there are quick way to convert the field? if so, what's the syntax.

thx,

john

cognos79
Posting Yak Master

241 Posts

Posted - 2006-12-12 : 16:50:16

try something like this

CONVERT(CHAR(5),getdate(),10)

latingntlman
Yak Posting Veteran

96 Posts

Posted - 2006-12-12 : 16:57:57

IT KINDA WORKS.... ALTHOUGH I NEED MONTH/YEAR AS MM/YYYY.

THIS IS GIVING ME MM/DD

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-12 : 17:01:15

Do the formatting in front-end application!

Peter Larsson
Helsingborg, Sweden

latingntlman
Yak Posting Veteran

96 Posts

Posted - 2006-12-12 : 17:09:12

THAT'S EXACTLY WHAT I DON'T WANNA DO.

I'M TRYING TO DO IT AT THE BACK-END.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-12 : 17:17:05

Response.Write FormatDateTime(..., n)

Text1.Text = Format(xxx, '....')

Peter Larsson
Helsingborg, Sweden

cognos79
Posting Yak Master

241 Posts

Posted - 2006-12-12 : 17:27:57

select convert(varchar,datepart(mm,getdate())) + '/' + convert(varchar,datepart(yyyy,getdate())) as 'result'

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-12 : 17:33:54

SELECT RIGHT(CONVERT(varchar, GETDATE(), 103), 7)

Peter Larsson
Helsingborg, Sweden

latingntlman
Yak Posting Veteran

96 Posts

Posted - 2006-12-13 : 09:53:31

THANK YOU ALL. I USED PESO'S AND IT WORKED.

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2006-12-13 : 09:54:40

THAT'S EXACTLY WHAT I DON'T WANNA DO.

I'M TRYING TO DO IT AT THE BACK-END.

Then you are patently wrong. The back end does NOT care about format or order. Format is a front end deal.

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-13 : 09:56:54

Unless he is writing a query to bcp out to a file.

Peter Larsson
Helsingborg, Sweden

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-12-13 : 10:12:12

quote:
Originally posted by Peso

Do the formatting in front-end application!

Peter Larsson
Helsingborg, Sweden

Exactly what I wanted to suggest

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-12-13 : 10:21:20

quote:
Originally posted by latingntlman

THAT'S EXACTLY WHAT I DON'T WANNA DO.

I'M TRYING TO DO IT AT THE BACK-END.

1 Do you have front end and dont want to format there?
2 Do you not have front end and export formatted dates to EXCEL or TEXT file?
3 If it is TEXT file, then using convert function is the only way.
Otherwise "Do it in the front end application" is What you should consider

Madhivanan

Failing to plan is Planning to fail

latingntlman
Yak Posting Veteran

96 Posts

Posted - 2006-12-13 : 11:16:27

It's outputting to an Excel spreadsheet. Although I agree with you guys, by formatting it to mm/yyyy in Excel, i still will have the month, date and year as value.

KenW
Constraint Violating Yak Guru

391 Posts

Posted - 2006-12-13 : 15:30:22

quote:
Originally posted by Peso

Do the formatting in front-end application!

Doesn't Madhi have this copyrighted? Do you owe him royalties now?

Ken

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-13 : 15:32:46

Oh Yes! I owe him big time

Peter Larsson
Helsingborg, Sweden

KenW
Constraint Violating Yak Guru

391 Posts

Posted - 2006-12-13 : 15:41:15

Hmmm... Should I collect a percentage from him too, for pointing this out?

Awww... I just realized that he'll probably pay in yak dollars, and I can't use them here.

Ken

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-12-14 : 05:57:40

quote:
Originally posted by KenW

quote:
Originally posted by Peso

Do the formatting in front-end application!

Doesn't Madhi have this copyrighted? Do you owe him royalties now?

Ken

Now-a-days Peso and Tan make my job easy by saying "Do it in the front end application"

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-14 : 06:50:30

At least it is a proficient postcount booster...

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources