Query Time

Question

Post reply

Query Time

Vijay Soni

SSC Veteran

Points: 235
More actions
May 4, 2005 at 2:22 am

#66413

I want to qury only time in HH:MM:SS AM/PM fromat from a datetime Field Or SmallDatetime Filed. Can anybody help me?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Rafter SSC-Addicted Points: 472 More actions · Answer 1

Try using the 'convert' function i.e -

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

Vijay Soni SSC Veteran Points: 235 More actions · Answer 2

Hi,

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

Good enough, but I need Am/Pm associated and do not want to use Left or right function

Frank Kalis SSC Guru Points: 111183 More actions · Answer 3

This is merely a presentational issue that you really should handle at the client.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

dougsx1 Valued Member Points: 64 More actions · Answer 4

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

amitelman SSC Enthusiast Points: 180 More actions · Answer 5

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