I have seen scores of expert developers getting perplexed with SQL Server in finding time only from datetime datatype. Let us have a quick glance look at the solution. Let us learn about how to get Time in Hour:Minute Format from a Datetime as well as to get Date Part Only from Datetime.
SQL Server 2000/2005
SELECT CONVERT(VARCHAR(8),GETDATE(),108) AS HourMinuteSecond, CONVERT(VARCHAR(8),GETDATE(),101) AS DateOnly GO
SQL Server 2008
SELECT CONVERT(TIME,GETDATE()) AS HourMinuteSecond, CONVERT(DATE,GETDATE(),101) AS DateOnly GO
I hope the above solution is clear to you all.
Quick Video on the same subject
Here is the video discussing the same concepts.
Additionally, if you want to get current datetime you can use following functions in place of datetime variable.
CURRENT_TIMESTAMP is a nondeterministic function. Views and expressions that reference this column cannot be indexed. CURRENT_TIMESTAMP can be used to print the current date and time every time that the report is produced. GETDATE is a nondeterministic function. Views and expressions that reference this column cannot be indexed. GETDATE can be used to print the current date and time every time that the report is produced.
Please leave a comment about Hour:Minute Format. I will be happy to read them.
Reference: Pinal Dave (https://blog.sqlauthority.com)
162 Comments. Leave new
Thanks man for the great blog it is really so informative .
But i guess the code for SQL 2000/2005 is missing the year part so
SELECT
CONVERT(VARCHAR(8),GETDATE(),108) AS HourMinuteSecond,
CONVERT(VARCHAR(8),GETDATE(),101) AS DateOnly
GO
Should be
SELECT
CONVERT(VARCHAR(8),GETDATE(),108) AS HourMinuteSecond,
CONVERT(VARCHAR(10),GETDATE(),101) AS DateOnly
GO
Regards,
Ziek
SELECT
CONVERT(VARCHAR(8),GETDATE(),108) AS HourMinuteSecond
Is the following code returning the time of the server in military format?
Yes. It is in 24 hour format
Is there any better way to display HHMM in SQL 2005?
SELECT substring(convert (nchar(5), getdate(),14),1,2) + substring(convert (nchar(5), getdate(),14),4,2)
Thanks,
Raj
Yes. Here is the shortcut
select replace(convert(varchar(5),getdate(),108),’:’,”)
short cut for SQL 2008
select CAST(GETDATE() as date) as DateOnly
declared arrivaltime varchar
declared completedtime varchar
values is 10:00 and 11:00
i want to find difference between the hours
thanks in advance
plz send to mail if possible
select datediff(hour,cast(arrivaltime as datetime),cast(completedtime as datetime)) from your_table
I have a similar requirement but I need the difference of minutes also.
That is, I have startTime as varchar
endTime as varchar
Values are 10:00 AM and 7:30 PM
I need to find the difference between these two times. How to do that? I tried the above said query but that gives only the difference in hours. I need the time also. In this case I need 09:30 as result.
Please help. Thanks
Try this
declare @startTime varchar(10),@endTime varchar(10)
select @startTime =’10:00 AM’, @endtime =’7:30 PM’
select convert(varchar(10),@endtime-cast(@starttime as datetime),108)
Dim FTime = DateTime.Parse(FromTime)
Dim TTime = DateTime.Parse(ToTime)
Dim TimeDiffetent = TTime – FTime
txtDifferent = TimeDifferent
Hi All,
how to maintainance beter SQL system, when we have a lot of transactions, how to make sure that it’s run ok when we have about 500 000 rows transactions a day ?
Pinal,
Great tip for handling only date portion, but I find the following to display date in YYYY-MM-DD format and will allowing sorting for this value
SELECT
CONVERT(VARCHAR(10),GETDATE(),120) AS DateOnly
GO
Note that if you use front end application, you should do formation there
hello all,
how can i retrieve only the hour?
select datepart(hour,getdate())
hello all,
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
alter PROCEDURE dbo.[GetClassInfo]
@TimeNow int,
@DayId nvarchar(MAX)
AS
BEGIN
SELECT TeacherId,ClassName from ClassInfo where
TimeId=@TimeNow and DayId=@DayId
END
GO
timenow has a hour and so i want timeid to be hour as well how do i go about converting timeid to just an integer hour value?
Can you post some sample data with expected output?
thanks in advance
Hello Frnds,
Please Try this may be its solve your problem:-
select substring(CONVERT(varchar,getdate(),100),12,LEN(CONVERT(varchar,getdate(),100)))
Output of this statement is ” 5:32PM ”
Thank you
It is as simple as
select right(CONVERT(varchar,getdate(),100),7)
Also leave formation to front end application
Thanks a lot…
Hello friends,
i have a scenario like below:
Need to extract data from a table from previous day 08:30 AM to current day 08:29 AM.
Thanks in advance.
Where
datecol>=dateadd(day,datediff(day,0,getdate()),’8:30′)-1 and
datecol<dateadd(day,datediff(day,0,getdate()),'8:30')+1
CONVERT(VARCHAR(8),GETDATE(),101) AS DateOnly
is cutting off the year part.
i think it should be:
CONVERT(VARCHAR(10),GETDATE(),101) AS DateOnly
Pinal, how would i convert a time like 4:23:23 AM into 04:23:23 AM. I mean it’s just padding zero’s if there isn’t a built in convert code. I want to use it with masked edit control of the ajax control toolkit. Stupid thing will not accept 4:23:23 AM as valid
right(‘0’+time,8)
GOOD ONE.
Hello there, i got time on my query 21:10:00 how to get only 20:10 ?
many thanks
thnks buddy
Hello, I am a newbie to SQL server. How do I use the time only retrieval function to get the time from a database field e.g. I have a field called sDateTime which is a datetime field
figured it out
CONVERT(TIME(0), Fieldname)
Thanks for the direction though
select convert(varchar(12),sDatetime,108) from table
Hi Madhivanan,
i want to calculate how much time spending in office
based on logintime and logout time,
condition is :9 hours perday
i want to display spending time ,reminingtime
plz help me
Hi Pinal,
Plz help me
Post some sample data and expected result
DECLARE @Logintime VARCHAR(36)
set @Logintime=’11:35am’
DECLARE @Logouttime VARCHAR(10)
DECLARE @diff VARCHAR(36),
@hr VARCHAR(30),
@min VARCHAR(2),
@sec VARCHAR(30),
@resttime varchar(36)
,@rtime VARCHAR(30)
DECLARE @NewLineChar AS CHAR(2)
SET @NewLineChar= CHAR(13) + CHAR(10)
SET @Logouttime = (SELECT right(Getdate(), 8)AS t)
SET @sec = Abs(Datediff(SECOND, @Logintime, @Logouttime))
SET @hr = @sec / 3600
SET @min = ( @sec – ( @hr * 3600 ) ) / 60
SET @sec = ( @sec – ( @hr * 3600 ) – ( @min * 60 ) )
IF CAST(@hr AS INT) < 10
SET @hr = '0' + @hr
IF CAST(@min AS INT) < 10
SET @min = '0' + @min
IF CAST(@sec AS INT) < 10
SET @sec = '0' + @sec
SET @diff = @hr + ':' + @min + ':' + @sec
print @diff
PRINT 'YOU ARE SPENDING IN OFFICE :'+' ' + @diff + ' ' + 'HOURS'
IF CAST(@hr AS INT) < 9
print 'YOU WILL GET LOSS OF PAY'
ELSE
PRINT 'SUCCESSFULLY CONSIDERED YOUR ATTENDENCE FOR THIS DAY '
Hi madhivanan
This is my query output is
08:16:00
YOU ARE SPENDING IN OFFICE : 08:16:00 HOURS
YOU WILL GET LOSS OF PAY
Wait For Rest of Time:
I want to display Rest of Time