SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Converting datetime to DD-Mon-YY Format
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

umertahir
Posting Yak Master

United Kingdom
145 Posts

Posted - 07/15/2009 :  06:53:06  Show Profile  Click to see umertahir's MSN Messenger address  Reply with Quote
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

Edited by - umertahir on 07/15/2009 07:05:08

madhivanan
Premature Yak Congratulator

India
22772 Posts

Posted - 07/15/2009 :  06:56:25  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
Go to Top of Page

umertahir
Posting Yak Master

United Kingdom
145 Posts

Posted - 07/15/2009 :  07:02:25  Show Profile  Click to see umertahir's MSN Messenger address  Reply with Quote
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

Go to Top of Page

bklr
Flowing Fount of Yak Knowledge

India
1693 Posts

Posted - 07/15/2009 :  07:10:01  Show Profile  Reply with Quote
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
Go to Top of Page

umertahir
Posting Yak Master

United Kingdom
145 Posts

Posted - 07/15/2009 :  07:12:03  Show Profile  Click to see umertahir's MSN Messenger address  Reply with Quote
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.
Go to Top of Page

bklr
Flowing Fount of Yak Knowledge

India
1693 Posts

Posted - 07/15/2009 :  07:17:04  Show Profile  Reply with Quote
see the links given,

select Getdate(),replace(convert(varchar(11),getdate(),106),' ','-'),
left(convert(varchar(10),getdate(),108),5)
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22772 Posts

Posted - 07/15/2009 :  07:19:37  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
Go to Top of Page

umertahir
Posting Yak Master

United Kingdom
145 Posts

Posted - 07/15/2009 :  07:20:13  Show Profile  Click to see umertahir's MSN Messenger address  Reply with Quote
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)

Go to Top of Page

bklr
Flowing Fount of Yak Knowledge

India
1693 Posts

Posted - 07/15/2009 :  07:23:59  Show Profile  Reply with Quote
welcome

see the links for formatting the dates and see that for display purpose do it in front end application as madhivanan suggested
Go to Top of Page

umertahir
Posting Yak Master

United Kingdom
145 Posts

Posted - 07/15/2009 :  07:25:27  Show Profile  Click to see umertahir's MSN Messenger address  Reply with Quote
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

Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30282 Posts

Posted - 07/15/2009 :  07:36:26  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"

Edited by - SwePeso on 07/15/2009 07:37:54
Go to Top of Page

Jeff Moden
Aged Yak Warrior

USA
651 Posts

Posted - 07/19/2009 :  20:07:29  Show Profile  Reply with Quote
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"

Go to Top of Page

umertahir
Posting Yak Master

United Kingdom
145 Posts

Posted - 07/20/2009 :  03:57:38  Show Profile  Click to see umertahir's MSN Messenger address  Reply with Quote
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"



Go to Top of Page

Jeff Moden
Aged Yak Warrior

USA
651 Posts

Posted - 07/20/2009 :  08:27:13  Show Profile  Reply with Quote
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"

Go to Top of Page

ckb11
Starting Member

2 Posts

Posted - 11/07/2012 :  16:36:18  Show Profile  Reply with Quote
quote:
Originally posted by bklr

see the links given,

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

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000