format date function HELP PLEASE

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)

format date function HELP PLEASE

Author

Topic

jennypretty
Yak Posting Veteran

96 Posts

Posted - 2004-10-14 : 11:05:09

Hello friends,
I 've trying to format the date in sql but I could not find DATE function I want.
I want the DATE format like this: Oct-04, Nov-04
All functions I found are return the full day, month, and year.
Does sql have a function to return month (3 digits), and year (2 digits) ONLY?
Thanks.
Jenny.

The stupid question is the question you don't ask.
www.single123.com

surefooted
Posting Yak Master

188 Posts

Posted - 2004-10-14 : 11:49:28

Nope, but you can create your own.

Create Function my_date(@date datetime)
returns char(6)

AS

BEGIN

return (convert(varchar(3),@date,100) + '-' + substring(convert(char(11), @date,100),10,2))

END

select dbo.my_date(getdate())

returns Oct-04

-Jon
Now a "Yak Posting Veteran".

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2004-10-14 : 12:01:45

No function for that, so you need to do formatting of the data yourself:

they ain't pretty


Select convert(VarChar(10), getdate(),7)

select convert(varchar(3), getdate(),7)+'-'+substring(cast(datepart(yy,convert(VarChar(10), getdate(),7)) as varchar(4)),3,2)

*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!

jennypretty
Yak Posting Veteran

96 Posts

Posted - 2004-10-14 : 12:19:58

I tested your function and it worked. When I applied the function to my real table, it doesn't recognize it.

select my_date(emp_hireddate())
from emp

Errors: 'emp_hireddate' is not a recognized function name.

Do you have any idea?

Thanks.

The stupid question is the question you don't ask.
www.single123.com

surefooted
Posting Yak Master

188 Posts

Posted - 2004-10-14 : 12:26:37

remove () behind emp_hiredate() so it looks like:

select my_date(emp_hireddate)
from emp

-Jon
Now a "Yak Posting Veteran".

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-10-14 : 12:45:41

you can also do:

Select left(datename(month,getdate()),3) +'-'+ convert(varchar,day(getdate()))

Corey

jennypretty
Yak Posting Veteran

96 Posts

Posted - 2004-10-14 : 14:02:18

It still showed errors. This is what I did:
I used query analyzer, clicked on database name, then click on Function, then create my_date function. Then, I click on view, then create this view:
CREATE VIEW emp_hiredate AS
SELECT my_date(emp_hiredate)
from emp

Errors: 'my_date' is not a recognized function name.

The stupid question is the question you don't ask.
www.single123.com

surefooted
Posting Yak Master

188 Posts

Posted - 2004-10-14 : 14:04:50

Use the owner_name.function_name([argument_expr]) syntax so if its owned by dbo, then:

select dbo.my_date(emp_hireddate())
from emp

-Jon
Now a "Yak Posting Veteran".

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-10-14 : 14:11:55

CREATE VIEW emp_hiredate AS
SELECT emp_hireDate = left(datename(month,emp_hiredate),3) +'-'+ convert(varchar,day(emp_hiredate))
from emp

Corey

jennypretty
Yak Posting Veteran

96 Posts

Posted - 2004-10-14 : 14:21:43

Both worked.
But it is a little strange... can some one help me to clarify this?
If I create another view not based on a function, then I don't have to include dbo...
Jenny.

The stupid question is the question you don't ask.
www.single123.com

surefooted
Posting Yak Master

188 Posts

Posted - 2004-10-14 : 14:25:41

Glad it worked for you. The function syntax is owner.function_name. I should have mentioned that earlier. sorry.

-Jon
Now a "Yak Posting Veteran".

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-10-14 : 15:34:16

format you dates at the presentation layer. do not do this in T-SQL. If you are writing reports or web pages, format your dates there.

- Jeff

jennypretty
Yak Posting Veteran

96 Posts

Posted - 2004-10-15 : 08:57:08

Oh, good point.
This is about reports and web pages. I didn't know that. Should I delete all the date formats and do it when writing web page templates?
Why don't I do this in T-sql?
thanks.
Jenny.

The stupid question is the question you don't ask.
www.single123.com

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-10-15 : 09:36:48

It is not T-SQL's job. SQL Server should return DATA -- it should return a date value to the front end. then the front end can do whatever it wants with it, display it any manner which it may need. Different front ends might want to display that same value differently.

There is a reason why T-SQL has very few formatting functions -- it is not supposed to! If you return foramtted data in T-SQL, you are no longer returning data with the proper datatypes, you are returning a bunch of VARCHAR's. As you have probably learned, dates stored in VARCHARs do not format or calculate properly unless you convert them BACK to datetime.

Just use SQL to return the data, use your front end to format it.

- Jeff

Kristen
Test

22859 Posts

Posted - 2004-10-16 : 01:52:58

Well, IMHO it depends ...

I have a client end tool that displays what it is given, and I can control the formatting from SQL. (I do of course understand that formatting at the client is usually better).

However, for a web site I would have to deploy new web pages AND new SProcs to achieve that. Whereas if I can say [MyDate] = CONVERT(varchar(17), MyDateColumn, 113) the web presentation layer doesn't have to do anything.

Of course, if I am using a configurable reporting tool then I would expect to able to right-click the column in some desaigner-tool and choose the presentation format. But .... lifes not always like that.

We do a lot of formation of HREF/URLs in SQL Select statements so that the presentation layer doesn't need to be too clever. Our presentation layer kinda meets me half way ...

Kristen

Kristen

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-10-16 : 10:05:54

quote:
Originally posted by Kristen

Well, IMHO it depends ...

However, for a web site I would have to deploy new web pages AND new SProcs to achieve that.

?? Your stored procs do not change if you want to change formatting, if you do formatting in the presentation layer. Not sure why you think they would need to change. they only need to change if you do foramtting WITHIN the stored proc.

- Jeff

Kristen
Test

22859 Posts

Posted - 2004-10-16 : 10:09:38

Sorry, my example was where I needed to change the logic of an SProc - return an extra column, for example.

Kristen

MATTXtwo
Starting Member

4 Posts

Posted - 2008-10-15 : 23:21:04

quote:
Originally posted by surefooted

Nope, but you can create your own.

Create Function my_date(@date datetime)
returns char(6)

AS

BEGIN

return (convert(varchar(3),@date,100) + '-' + substring(convert(char(11), @date,100),10,2))

END

select dbo.my_date(getdate())

returns Oct-04

-Jon
Now a "Yak Posting Veteran".

Can I select data from table and changed format date as
'12 SEPTEMBER 2008'...how to do that

BRINGING LITE CONTENTS

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-10-16 : 00:29:16

quote:
Originally posted by MATTXtwo

quote:
Originally posted by surefooted

Nope, but you can create your own.

Create Function my_date(@date datetime)
returns char(6)

AS

BEGIN

return (convert(varchar(3),@date,100) + '-' + substring(convert(char(11), @date,100),10,2))

END

select dbo.my_date(getdate())

returns Oct-04

-Jon
Now a "Yak Posting Veteran".

Can I select data from table and changed format date as
'12 SEPTEMBER 2008'...how to do that

BRINGING LITE CONTENTS

Don't hijack threads that are 4 years old.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources