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
Dear Pinal,
I got the answer.
SELECT CONVERT(VARCHAR(5),GETDATE(),108) AS HourMinuteSecond
Thank You
Regards
Vivek
I want 10.30 PM not 22:30
Hi Vasu,
You can display time in AM/PM format as:
DECLARE @dt DATETIME
SELECT @dt = ‘2009-11-08 20:00’
SELECT CONVERT(VARCHAR(5),@dt,108) + ‘ ‘ + RIGHT(CONVERT(VARCHAR,@dt, 109),2)
Thanks,
Tejas
Hi,
Ignore my last comment
Please check it out:
Thanks,
Tejas
hi tejas
i need to combine two column information into one column can u plz help me
thanks
Nishar
Hi Nishar,
Can you post some sample data that you have with expected OUTPUT.
Thanks,
Tejas
select column1+column2 as Column3 from tablename
Would wish to be pointed to write an sql statement that return hourly records of a given day.
Looking for 24 records for every hours of the day?
Yes so as to return the hourly count
Try this:
DECLARE @dates TABLE (dt DateTime)
INSERT INTO @dates (dt) SELECT ‘2009-10-01’
SELECT
DATEADD(hour, number, dt) AS HourPart
FROM @dates
CROSS JOIN master..spt_values s
WHERE type = ‘p’ AND number between 0 AND 23
/*
HourPart
———————–
2009-10-01 00:00:00.000
2009-10-01 01:00:00.000
2009-10-01 02:00:00.000
2009-10-01 03:00:00.000
2009-10-01 04:00:00.000
2009-10-01 05:00:00.000
2009-10-01 06:00:00.000
2009-10-01 07:00:00.000
2009-10-01 08:00:00.000
2009-10-01 09:00:00.000
2009-10-01 10:00:00.000
2009-10-01 11:00:00.000
2009-10-01 12:00:00.000
2009-10-01 13:00:00.000
2009-10-01 14:00:00.000
2009-10-01 15:00:00.000
2009-10-01 16:00:00.000
2009-10-01 17:00:00.000
2009-10-01 18:00:00.000
2009-10-01 19:00:00.000
2009-10-01 20:00:00.000
2009-10-01 21:00:00.000
2009-10-01 22:00:00.000
2009-10-01 23:00:00.000
*/
Hello all – I am a newbie,
can someone tell me how do I modify this to remove the ‘AM’ from the result
SELECT CONVERT(VARCHAR(10), DATEADD(d,+1,GETDATE()),101)+ ‘ ‘ + RIGHT (CONVERT(VARCHAR, GETDATE(), 100),7)
Hi Donna,
Could you please let us know, in which format do you want the datetime?
If you just want to remove AM/PM from the above query. You can make it possible as:
SELECT CONVERT(VARCHAR(10), DATEADD(d,+1,GETDATE()),101)+ ‘ ‘ +
LEFT(RIGHT (CONVERT(VARCHAR, GETDATE(), 100),7),5)
Its better if you post in which format do you want result.
Thanks,
Tejas
SQLYoga.com
@Donna
Please look at the list of date conversions directly supported by SQL Server: https://docs.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-2017
SELECT CONVERT(VARCHAR, GETDATE(), 107) + ‘ ‘ + CONVERT(VARCHAR, GETDATE(), 108)
You are my hero
Note that if you format dates using sql, all your DATEs become VARCHARs which can’t be used to make use of date related functions until re-converted back to DATETIME
If you use front end application, you should do formation there
Madhivanan
In SQL server 2008, the time and date functions are separated. This article is very useful!!!
I created this function to return AM/PM time from a datetime variable. I use it in a SELECT statement to nicely return AM/PM separately from the date.
CREATE FUNCTION [dbo].[funcAMPMTimeFromDate]
(
@MyDateTimeValue as datetime
)
RETURNS char(8)
AS
BEGIN
— Declare the return variable here
DECLARE @MyTime as char(8)
SET @MyTime = case when (datepart(hh,@MyDateTimeValue) > 12)
then rtrim(cast((datepart(hh,@MyDateTimeValue)-12) as char(2))) + ‘:’ +
case when (datepart(mi,@MyDateTimeValue)<10) then '0' + cast(datepart(mi,@MyDateTimeValue) as char(1)) + ' PM'
else cast(datepart(mi,@MyDateTimeValue) as char(2)) + ' PM' END
when (datepart(hh,@MyDateTimeValue) = 0)
then '12:' +
case when (datepart(mi,@MyDateTimeValue)<10) then '0' + cast(datepart(mi,@MyDateTimeValue) as char(1)) + ' AM'
else cast(datepart(mi,@MyDateTimeValue) as char(2)) + ' AM' END
else cast(datepart(hh,@MyDateTimeValue) as char(2)) + ':' +
case when (datepart(mi,@MyDateTimeValue)<10) then '0' + cast(datepart(mi,@MyDateTimeValue) as char(1)) + ' AM'
else cast(datepart(mi,@MyDateTimeValue) as char(2)) + ' AM' END END
— Return the result of the function
RETURN @MyTime
END
You dont need to complicate that much
It is as simple as
select right(convert(char(20),getdate(),0),7)
cast(convert(int,getdate())as datetime)
This, I think would be the simplest way to display only the date and send it as a date type value.
Sorry, not to display but for computations that require the time be removed or set to “0:00”
This not only removes time part but also increases day by 1
The correct and effecient method is
dateadd(day,datediff(day,0,getdate()),0)
Using cast(convert(int,getdate())as datetime) did not add one to the date. It simply removes the fraction of the date without rounding.
Did you test your solution?
Ok
See if you get the same number for these two
select
convert(int,getdate()),
datediff(day,0,getdate())
Hi,
i have the following data from an IIS log file:
cs-username (multiple user’s)
date (only 1 day of data)
time (displayed as Hour:Minute:Second AM or PM)
I am trying to capture the first time the user access a site. My problem is that IIS captures mulitple times during the day for certain users. How can I query where the username will pull up once with the time showing only once.
Thanks,
Tiffany
Hi,
i have the following data from an IIS log file:
cs-username (multiple user’s)
date (only 1 day of data)
time (displayed as Hour:Minute:Second AM or PM)
I am trying to capture the first time the user access a site. My problem is that IIS captures mulitple times during the day for certain users. How can I query where the username will pull up once with the time showing only once.
Thanks,
Antiffia
Hi All,
I select query in SQL Server 2005
with filed round(100/Exchrate,2)
then result :18960.00000000000000000000000
but I want the result is : 18960.00
Kindly help me.
Regards,
Nam Chau
Use
cast(100/Exchrate as decimal(14,2))
great stuff.