Try using the 'convert' function i.e -

select convert(char(10),getdate(),8)

Hi,

"select convert(char(10),getdate(),8)"

Here is a UDF I was playing with just last night. I'm trying to get time data in a HH:MM AM/PM format from the SQL server.

USE your_db

GO

IF EXISTS (SELECT * FROM dbo.SYSOBJECTS WHERE ID = OBJECT_ID(N'[dbo].[GetAMPM]')
           AND XType IN (N'FN', N'IF', N'TF')) DROP FUNCTION [dbo].[GetAMPM]
GO

CREATE FUNCTION GetAMPM

(@thedate datetime)

RETURNS varchar(20)
AS
BEGIN

Declare
@thenewdate  varchar(20),
@thehour int,
@theminute varchar(20),
@ampm  varchar(20)

SET @thehour = DATEPART(hh, @thedate)

SET @thehour = (case when DATEPART(hh, @thedate)>12 then DATEPART(hh, @thedate)-12
  when DATEPART(hh, @thedate) = 12 then DATEPART(hh, @thedate)
  Else DATEPART(hh, @thedate) End)

SET @theminute = (case when DATEPART(mi, @thedate)= 0 then '00'
   when DATEPART(mi, @thedate) < 10 then '0' + cast(DATEPART(mi, @thedate) as varchar)
   Else cast(DATEPART(mi, @thedate) as varchar) End)

SET @ampm = (case when DATEPART(hh, @thedate) >= 12 then 'PM' Else 'AM' End)

SET @thenewdate = cast(@thehour as varchar) + ':' + @theminute + ' ' + @ampm

RETURN (@thenewdate)

END

Vijay,

it is not clear to me why you "...do not want to use left or right function". Are you looking for some new features in Yukon?

Anyway, here are statements that might be helpfull:

declare @date datetime, @smalldate smalldatetime
SELECT @date=getdate(),@smalldate=getdate()

SELECT right('0'+ltrim(substring(CONVERT(varchar,@date,22),10,11)),11)
SELECT right('0'+ltrim(substring(CONVERT(varchar,@smalldate,22),10,11)),11)

09:14:11 AM
09:14:00 AM

--This one does not use left or right.

SELECT reverse(substring(rtrim(substring(reverse(CONVERT(varchar,GETDATE(),22)),1,11))+'0',1,11))

09:14:11 AM