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
Hello Sir.
I want to know that how to get only Hour & minute from Datetime field.
I have to make one Window service so that i need to check Hour and minute from database.
please help me in this
SELECT DATEPART(HOUR,GETDATE()),DATEPART(MINUTE,GETDATE())
Try this
select Convert(varchar(5),getdate(),114)
select convert(varchar,DATEPART(hh,getdate())-12) + ‘ : ‘ + convert(varchar,DATEPART(N,getdate())) +’ ‘+ RIGHT(CONVERT(VARCHAR,GETDATE(),109),2)
yes
this formul select convert(varchar,DATEPART(hh,getdate())) + ‘:’ + convert(varchar,DATEPART(N,getdate())) is what i seach since 1 hour.
Thanks very much.
Do you know how add 0 before hour?
For example 1;30 -> 01:30
and the same thing with minute?
thanks
Use convert function with style 108. It will append it automatically
Thanks you but it s a wrong way.
I try
SELECT (convert(varchar,DATEPART(hh,getdate())) + ‘:’ + convert(varchar,DATEPART(N,getdate()),108)) as t
but i obtained
9:2
I need 09:02
This way is good for Hour.
SELECT
CAST(
CASE WHEN LEN(DATEPART(hh,getdate())) = 1
THEN ‘0’ + CAST( DATEPART(hh,getdate()) AS varchar)
ELSE CAST( DATEPART(hh,getdate())AS varchar) END
AS varchar)
But it isn’t smart
You can easily do this
SELECT convert(varchar(5),getdate(),108) as t
It’s work.
Thanks you very much.
Hello friends
I have a case
Where do I get a month records from a table and the condition is that q is the time 22 pm to 6 am the following day,
so far I have this.
DATEPART (hh, date) = 22 and date> = ‘20110901 ‘and date <'20111001'
But I have no results, can you please help with syntax,
Hello friends
I have a case
Where do I get a month records from a table and the condition is that the hours are from 22 pm to 6 am the following day,
so far I have this.
DATEPART (hh, date) = 22 and date> = ‘20110901 ‘and date <'20111001'
But I have no results, can you please help with syntax,
where datecol>’20110901 22:00:00′ and date_col<'20111001 06:00:00'
I want to get the Date and the hour part.
To get the date i use select convert(varchar(10),getdate(),111) which gives me
2011/11/25 as Output.
But i want the hour part as well something like this:
2011/11/25 11 AM
Here 11 Am is the Hour part.How cn i achieve this?
Select Convert(varchar(10),getdate(),101)
union all
select GETDATE()
result :
2011-12-05 00:00:00.000
2011-12-05 07:08:38.853
Required :
2011-12-05
2011-12-05 07:08:38.853
Can you please help me to solve the problem.
It is not possible. The implicit convertion happens. Why do you want to do this?
Hi all
I have checkin date as varchar(10)
as well as checkout date as varchar(10)
and i have got all the record in the table between this dates including this date field
so what i do
You need to convert these two varchars into datetime before doing any comparison
but i got the same result
using the query like this
Where checkinDate>=’12/12/2011′ And checkoutDate,=’12/31/2012′
Hi,
This is a neat site for information. Can anybody help us with the following: I’m trying to get the time part of a DateTime then convert it to minutes.
Eg. DateTime – 2012-03-20 07:30:00.000
Minutes – 450
What would be the most efficient way of getting the minutes in sql?
Thanks in advance
Sharon
Any Idea how to display Date & Time in following format:
“02/28/2012 12:59 AM ET”
??
its: “02/28/2012 11:59 AM ET”
I am using the exact same way to display date only but it shows in our application dashboard with full DateTime stamp in data grid..
i typed in SQL server 2005,
SELECT CONVERT(VARCHAR(10),GETDATE(),101) as Date
Any idea????
Better you do the date format in the application itself
Pinal,
Once again you have come through with the goods. You are the man.
Good solution…Thanx Pinal
This is a BAD solution – converting to a string and hoping to accurately lop off the time portion with a substring, then converting it back, is a HACK.
To get the response like 3:16PM
SELECT RIGHT(LTRIM(RIGHT(Convert(varchar,GETDATE(),100),8)),8) AS ONLY_TIME
I have a table with a datetime field, and I would like to find just records from today, after 10:15am.
I have :
where TIME_STAMP >= cast(floor(cast(GETDATE() as float)) as datetime)
and LEFT(CONVERT(TIME(2),TIME_STAMP) ,5) > ’10:15′
which works fine in SQL Server 10, but our prod database is SQL Server 9.
Any ideas on an efficient way to do this?
SELECT CONVERT(VARCHAR(19), GETDATE(), 1)
+ ‘ ‘ +
RIGHT(CONVERT(VARCHAR(17), GETDATE(), 9),5)
–REPLACE(RIGHT(CONVERT(VARCHAR(50), GETDATE(), 100),7),RIGHT(RIGHT(CONVERT(VARCHAR(50), GETDATE(), 100),7),2),”)
+’ ‘+
RIGHT(CONVERT(VARCHAR(19), GETDATE(), 100),2)AS ‘Date’
Result will be : 08/23/12 4:16 PM
i want 10-OCT-2012 10:50:10 PM
Dear All,
Can any one help me,
i have two columns of one table as @ABC as DateTime and @xyz as DateTime – Datatype
i want to subtract only time and i am trying like this
for ABC = 21/02/2012 6:10:00 PM and XYZ = 01/01/2001 12:00:00 AM -> 1st Row
CONVERT(varchar(10), dbo.checkingtime.ABC – dbo.checkingtime.XYZ, 108)
and i am getting the result as 18:10 but i want the result as 05:50 in Hours and Minutes only
is it possible ? ? ?
Awaiting from the HelpingNature People