format the DateTime in SQL Server 2000

Bandi
Posted by Bandi under Sql Server category on | Points: 40 | Views : 359
The datetime column will store value in the format of YYYY-MM-DD HH:Mi:SS.nnnn

Especially in SQL Server 2000, we have to convert the datetime column to display in specific format


--SQL Server 2000
--My Table strct:
--===========
create table SampleTable
(
id int identity,
from_p varchar(100),
to_p varchar(100),
timin DATETIME
)


--My Expecting OUTPUT:
--================
insert into SampleTable values('a','b','6:05 AM')

SELECT id, from_p, to_p, timin, RIGHT(CONVERT(VARCHAR(20), timin, 100), 7) TimeFormat FROM SampleTable
GO
DECLARE @foo TABLE
(
dt DATETIME
)


INSERT @foo(dt) VALUES('20011031') -- YYYYMMDD ( 31st Oct, 2001)
INSERT @foo(dt) VALUES('3:25 PM') -- Time value HH:MI PM

SELECT RIGHT(CONVERT(VARCHAR(20), dt, 100), 7) [HH:Mi AM/PM], dt FROM @foo
/*
--Results
-------
HH:Mi AM/PM dt
12:00AM 2001-10-31 00:00:00.000
3:25PM 1900-01-01 15:25:00.000
*/

DROP TABLE SampleTable

Comments or Responses

Login to post response