| Author |
 Topic  |
|
|
umertahir
Posting Yak Master
United Kingdom
145 Posts |
 Posted - 07/15/2009 : 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 |
Edited by - umertahir on 07/15/2009 07:05:08
|
|
|
madhivanan
Premature Yak Congratulator
India
22460 Posts |
Posted - 07/15/2009 : 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
United Kingdom
145 Posts |
Posted - 07/15/2009 : 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
Flowing Fount of Yak Knowledge
India
1693 Posts |
|
|
umertahir
Posting Yak Master
United Kingdom
145 Posts |
Posted - 07/15/2009 : 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
Flowing Fount of Yak Knowledge
India
1693 Posts |
Posted - 07/15/2009 : 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
India
22460 Posts |
Posted - 07/15/2009 : 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
United Kingdom
145 Posts |
Posted - 07/15/2009 : 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
Flowing Fount of Yak Knowledge
India
1693 Posts |
Posted - 07/15/2009 : 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
United Kingdom
145 Posts |
Posted - 07/15/2009 : 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
Sweden
29138 Posts |
Posted - 07/15/2009 : 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" |
Edited by - SwePeso on 07/15/2009 07:37:54 |
|
|
|
Jeff Moden
Aged Yak Warrior
USA
643 Posts |
Posted - 07/19/2009 : 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
United Kingdom
145 Posts |
Posted - 07/20/2009 : 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
USA
643 Posts |
Posted - 07/20/2009 : 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 - 11/07/2012 : 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)
|
|
|
| |
Topic |
|
Converting datetime to DD-Mon-YY Format
