Converting datetime to DD-Mon-YY 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 2005 Forums

Transact-SQL (2005)

Converting datetime to DD-Mon-YY Format

Author

Topic

umertahir
Posting Yak Master

154 Posts

Posted - 2009-07-15 : 06:53:06

How do I convert a datetime column into the format DD-Mon-YY?

The stored date , in datetime format, is: 2009-01-21 20:50:00.000
I want to display in column Date as : 21-Jan-09
and
column Time: 20:50

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-07-15 : 06:56:25

quote:
Originally posted by umertahir

How do I convert a datetime column into the format DD-MMM-YY?

The stored date , in datetime format, is: 2009-01-21 20:50:00.000
I want to display in column Date as : 21-Jan-09
and
column Time: 20:50

Where do you want to show converted date?

Madhivanan

Failing to plan is Planning to fail

umertahir
Posting Yak Master

154 Posts

Posted - 2009-07-15 : 07:02:25

In SELECT statement:


SELECT	CONVERT(nvarchar(30), tblEBVRun.datRunDate, 107) AS Date, tblEBVRun.datRunDate AS [Time]

quote:
Originally posted by madhivanan

quote:
Originally posted by umertahir

How do I convert a datetime column into the format DD-MMM-YY?

The stored date , in datetime format, is: 2009-01-21 20:50:00.000
I want to display in column Date as : 21-Jan-09
and
column Time: 20:50

Where do you want to show converted date?

Madhivanan

Failing to plan is Planning to fail

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-07-15 : 07:10:01

see this links
http://www.sql-server-helper.com/tips/date-formats.aspx
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=80563

for display purpose u can do it in front end application also

umertahir
Posting Yak Master

154 Posts

Posted - 2009-07-15 : 07:12:03

I do not want to use DATEPART which is the only way I can think of. If there is anyway I can use CONVERT to do the job?

I tried the following code:


SELECT	CONVERT(nvarchar(30), CAST('2009-01-21 20:50:00.000' AS Datetime), 107) AS Date,
		DATEPART(hh, '2009-01-21 20:50:00.000') AS [Time]

Output:

Date: Jan 21, 2009
Time: 20

As I said I don't want to use datepart.

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-07-15 : 07:17:04

see the links given,

select Getdate(),replace(convert(varchar(11),getdate(),106),' ','-'),
left(convert(varchar(10),getdate(),108),5)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-07-15 : 07:19:37

quote:
Originally posted by umertahir

In SELECT statement:

SELECT CONVERT(nvarchar(30), tblEBVRun.datRunDate, 107) AS Date, tblEBVRun.datRunDate AS [Time]

quote:
Originally posted by madhivanan

quote:
Originally posted by umertahir

How do I convert a datetime column into the format DD-MMM-YY?

The stored date , in datetime format, is: 2009-01-21 20:50:00.000
I want to display in column Date as : 21-Jan-09
and
column Time: 20:50

Where do you want to show converted date?

Madhivanan

Failing to plan is Planning to fail

I meant "Do you want to show converted date in front end application?"

Madhivanan

Failing to plan is Planning to fail

umertahir
Posting Yak Master

154 Posts

Posted - 2009-07-15 : 07:20:13

Thats it.. thanks man..it works

quote:
Originally posted by bklr

see the links given,

select Getdate(),replace(convert(varchar(11),getdate(),106),' ','-'),
left(convert(varchar(10),getdate(),108),5)

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-07-15 : 07:23:59

welcome

see the links for formatting the dates and see that for display purpose do it in front end application as madhivanan suggested

umertahir
Posting Yak Master

154 Posts

Posted - 2009-07-15 : 07:25:27

Just wanted to create a VIEW for the client which will be displayed on the website.

quote:
Originally posted by madhivanan

quote:
Originally posted by umertahir

In SELECT statement:

SELECT CONVERT(nvarchar(30), tblEBVRun.datRunDate, 107) AS Date, tblEBVRun.datRunDate AS [Time]

quote:
Originally posted by madhivanan

quote:
Originally posted by umertahir

How do I convert a datetime column into the format DD-MMM-YY?

The stored date , in datetime format, is: 2009-01-21 20:50:00.000
I want to display in column Date as : 21-Jan-09
and
column Time: 20:50

Where do you want to show converted date?

Madhivanan

Failing to plan is Planning to fail

I meant "Do you want to show converted date in front end application?"

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-15 : 07:36:26

DATETIME doesn't have a format, other than the internal decimal format.
Everything else is just a formatted representation of the datetime value.

DECLARE	@Now DATETIME

SET	@Now = GETDATE()

SELECT	@Now, 
	REPLACE(CONVERT(CHAR(9), @Now, 6), ' ', '-') AS theDate,
	CONVERT(CHAR(5), @Now, 8) AS theTime

N 56°04'39.26"
E 12°55'05.63"

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2009-07-19 : 20:07:29

quote:
Originally posted by umertahir

Just wanted to create a VIEW for the client which will be displayed on the website.

Again... why does it have to be formatted? Where will the output of the view be used?

I take it you haven't read the links that were provided.

--Jeff Moden

"Your lack of planning DOES constitute an emergency on my part... SO PLAN BETTER! "
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row"

umertahir
Posting Yak Master

154 Posts

Posted - 2009-07-20 : 03:57:38

As I said above "Just wanted to create a VIEW for the client which will be displayed on the website."
I did read the links and they were very helpful.

Thanks

quote:
Originally posted by Jeff Moden

quote:
Originally posted by umertahir

Just wanted to create a VIEW for the client which will be displayed on the website.

Again... why does it have to be formatted? Where will the output of the view be used?

I take it you haven't read the links that were provided.

--Jeff Moden
"Your lack of planning DOES constitute an emergency on my part... SO PLAN BETTER! "
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row"

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2009-07-20 : 08:27:13

Thanks... sorry I missed the previous explanation.

--Jeff Moden

"Your lack of planning DOES constitute an emergency on my part... SO PLAN BETTER! "
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row"

ckb11
Starting Member

2 Posts

Posted - 2012-11-07 : 16:36:18

quote:
Originally posted by bklr

see the links given,

select Getdate(),replace(convert(varchar(11),getdate(),106),' ','-'),
left(convert(varchar(10),getdate(),108),5)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources