How to get Time from Getdate()

Question

Post reply

How to get Time from Getdate()

dchitm

SSC Veteran

Points: 234
More actions
December 17, 2003 at 12:16 pm

#106958

I am trying to get the time value in a 'hh:mm:ss am/pm' format from the getdate() function, can anyone please tell me how to achieve that? Many thanks!

Viewing 12 posts - 1 through 11 (of 11 total)

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

ThomasH SSCertifiable Points: 7437 More actions · Answer 1

Select Convert(Varchar(8), GetDate(), 8)

Once you understand the BITs, all the pieces come together

dchitm SSC Veteran Points: 234 More actions · Answer 2

Thanks Thomas for the reply, the below statement outputs the time in 24h, but I needed it to display in hh:mm:ss AM/PM format.

--------------------------------------------

Select Convert(Varchar(8), GetDate(), 8)

[/quote]

ThomasH SSCertifiable Points: 7437 More actions · Answer 3

Select SubString(Convert(Varchar(25), GetDate(), 9), 13, 8) + Right(Convert(Varchar(25), GetDate(), 9), 1) + 'M'

Once you understand the BITs, all the pieces come together

Jonathan SSC-Insane Points: 20427 More actions · Answer 4

SELECT RIGHT(STUFF(CONVERT(char(26),GETDATE(),9),21,4,' '),10)

--Jonathan

itboy Valued Member Points: 66 More actions · Answer 5

How about something like:

print Convert( Char(30), GetDate(), 108)

print Convert( Char(30), GetDate(), 109)

See BOL for a complete list with format numbers

ChrisM Valued Member Points: 63 More actions · Answer 6

Johnathan's method works very well, but a slight amendment is required otherwise it cuts the first character off the front. The command should read :

SELECT RIGHT(STUFF(CONVERT(char(26),GETDATE(),9),21,4,' '),11)

Thanks Jonathan. That's a concise answer to a frequent problem.

morrijl SSC Journeyman Points: 94 More actions · Answer 7

I have a need to use many date formats. The following SP lists the conversion codes and the length of the output.

CREATE procedure pDisplayDateConversionStyles as

print 'datetime to Varchar conversion codes'

print 'syntax: convert(varchar(length),getdate(),code)'

print ''

declare @i tinyint

declare @Now datetime

set @Now = getdate()

set @i = 0

while @i < 15

begin

print 'Length: ' + cast(len(convert(varchar(35),@Now,@i)) as varchar(3)) + 'Conversion code: ' + str(@i,3,0) + ' Returns: ' + convert(varchar(35),@Now,@i)

set @i = @i + 1

end

print 'Length: ' + cast(len(convert(varchar(35),@Now,20)) as varchar(3)) + 'Conversion code: ' + str(20,3,0) + ' Returns: ' + convert(varchar(35),@Now,20)

print 'Length: ' + cast(len(convert(varchar(35),@Now,21)) as varchar(3)) + 'Conversion code: ' + str(21,3,0) + ' Returns: ' + convert(varchar(35),@Now,21)

set @i = 100

while @i < 115

begin

print 'Length: ' + cast(len(convert(varchar(35),@Now,@i)) as varchar(3)) + 'Conversion code: ' + str(@i,3,0) + ' Returns: ' + convert(varchar(35),@Now,@i)

set @i = @i + 1

end

print 'Length: ' + cast(len(convert(varchar(35),@Now,120)) as varchar(3)) + 'Conversion code: ' + str(120,3,0) + ' Returns: ' + convert(varchar(35),@Now,120)

print 'Length: ' + cast(len(convert(varchar(35),@Now,121)) as varchar(3)) + 'Conversion code: ' + str(121,3,0) + ' Returns: ' + convert(varchar(35),@Now,121)

print 'Length: ' + cast(len(convert(varchar(35),@Now,126)) as varchar(3)) + 'Conversion code: ' + str(126,3,0) + ' Returns: ' + convert(varchar(35),@Now,126)

print 'Length: ' + cast(len(convert(varchar(35),@Now,130)) as varchar(3)) + 'Conversion code: ' + str(130,3,0) + ' Returns: ' + convert(varchar(35),@Now,130)

print 'Length: ' + cast(len(convert(varchar(35),@Now,131)) as varchar(3)) + 'Conversion code: ' + str(131,3,0) + ' Returns: ' + convert(varchar(35),@Now,131)

billkamp SSC Enthusiast Points: 107 More actions · Answer 8

select convert(char(8),getdate(),112) as yyyymmdd

select convert(char(8),getdate(),108) as [hh:mm:ss]

select convert(char(8),getdate(),1) as [mm/dd/yy]

outputs

yyyymmdd

--------

20031218

(1 row(s) affected)

hh:mm:ss

--------

11:28:57

(1 row(s) affected)

mm/dd/yy

----------

12/18/03

(1 row(s) affected)

The options are almost endless. See BOL->"Cast and Convert"

maijoe SSC Enthusiast Points: 140 More actions · Answer 9

You still can you Johnathan's method:

SELECT RIGHT(STUFF(CONVERT(char(26),GETDATE(),9),21,4,' '),11)

Minh Vu

covitz Old Hand Points: 322 More actions · Answer 10

covitz

Old Hand

Points: 322

December 18, 2003 at 1:30 pm

#487061

Thanks MORRIJL for your SP. It comes in handy.

Rohit @ SQL SSC Eights! Points: 938 More actions · Answer 11

Can be used this way also

quote:
Create Function DateOnly(@dt DateTime)
returns Datetime
as
begin
return Cast(convert(varchar(8),@dt,1) AS datetime)
end
CREATE Function TimeOnly(@dt DateTime)
returns datetime
as
begin
return Cast(convert(varchar(8),@dt,8) AS datetime)
end
Select dbo.Dateonly(getdate()),dbo.TimeOnly(getdate())

Rohit