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
Hi
I want to convert ‘2013-01-15 11:23:49.930′
output =’2013-01-15 11:00:00.000’
Please help.
Hi ALL,
i want to convert dateformat ‘2013-01-15 11:23:34.930’ into 2013-01-15 11:00:00.000 this format please help.
select dateadd(hour,datediff(hour,0,date_col),0) from your_table
sir how to find sum (datetime) for group by operation
SUM will work only form number datatypes
I wrote a small function which returns you table having Time Diffence as on of its attribute. Function is as follow:-
CREATE FUNCTION dbo.TimeDiff (@STARTDATE datetime,@ENDDATE datetime)
RETURNS @TIMEDIFF TABLE (ID int IDENTITY(1, 1) NOT NULL, TimeDiff TIME NULL) AS
BEGIN
DECLARE @24DATE DATETIME
SET @24DATE = ’23:59:59.000′
IF (@STARTDATE > @ENDDATE)
INSERT INTO @TIMEDIFF
SELECT DATEADD (SECOND,1, CONVERT(TIME(0), ( @24DATE -(@ENDDATE – @STARTDATE))))
ELSE
INSERT INTO @TIMEDIFF
SELECT CONVERT(TIME(0), ( (@ENDDATE – @STARTDATE)))
RETURN
END
GO
SELECT * FROM TimeDiff(’10:15:45′,’14:00:15′)
Result
ID TimeDiff
1 03:44:30.0000000
I wanted to be able to format dates in a similar way to the VB6 FORMAT() function where you give it a date and a picture of the date and it returns a string in that format.
I have written this as a SQL function and use it quite often
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(‘Format’) AND type in (N’FN’, N’IF’, N’TF’, N’FS’, N’FT’)) DROP FUNCTION Format
GO
Create Function Format(@Date DateTime,@Fmt Char(30)) Returns Char(30) as
Begin
— @Fmt can be dd/mm/yyyy, yyyy-mm-dd, dd-mmm-yyyy, dd/mm/yy, ddd dd/mm/yyyy, dddd dd-mmmm-yyyy, dddd mmmm dd, yyyy etc
— @Fmt can also include HH:mm, HH:mm:ss, HH:mm:ss:ttt formats – always in 24 hour format at this stage
— FD is First Day-of-month, LD is Last Day-of-month, FYYY is financial year, FM is month in financial year, MN is minutes of hour
Select @Fmt=Replace(@Fmt,’FD’,’01’);
Select @Fmt=Replace(@Fmt,’LD’,right(rtrim(cast(100+Day(DateAdd(Day,-1,DateAdd(Month,1,DateAdd(Day,1-Day(@Date),@Date)))) as Char(3))),2));
Select @Fmt=Replace(@Fmt,’FM’,Right(RTrim(Cast(100+Case When Month(@Date)>6 then Month(@Date)-6 else Month(@Date)+6 End as char(3))),2));
Select @Fmt=Replace(@Fmt,’FYYY’,Right(RTrim(Cast(10000+Case When Month(@Date)>6 then Year(@Date) else Year(@Date)-1 End as char(5))),4));
Select @Fmt=Replace(@Fmt,’FY’,Right(RTrim(Cast(10000+Case When Month(@Date)>6 then Year(@Date) else Year(@Date)-1 End as char(5))),2));
Select @Fmt=Replace(@Fmt,’HH’,left(Cast(Cast(@Date as Time) as char(10)),2));
Select @Fmt=Replace(@Fmt,’:MM’,SubString(Cast(Cast(@Date as Time) as char(12)),3,3));
Select @Fmt=Replace(@Fmt,’MN’,SubString(Cast(Cast(@Date as Time) as char(12)),4,2));
Select @Fmt=Replace(@Fmt,’SS’,SubString(Cast(Cast(@Date as Time) as char(12)),7,2));
Select @Fmt=Replace(@Fmt,’TTT’,SubString(Cast(Cast(@Date as Time) as char(12)),10,3));
Select @Fmt=Replace(@Fmt,’TT’,SubString(Cast(Cast(@Date as Time) as char(12)),10,2));
Select @Fmt=Replace(@Fmt,’DDDD’,DATENAME(dw,@Date));
Select @Fmt=Replace(@Fmt,’DDD’,Left(DATENAME(dw,@Date),3));
Select @Fmt=Replace(@Fmt,’DD’,Right(Rtrim(cast(100+day(@Date) as Char(3))),2));
Select @Fmt=Replace(@Fmt,’MMMM’,DATENAME(month,@Date));
Select @Fmt=Replace(@Fmt,’MMM’,Left(DATENAME(month,@Date),3));
Select @Fmt=Replace(@Fmt,’MM’,Right(Rtrim(cast(100+month(@Date) as Char(3))),2));
Select @Fmt=Replace(@Fmt,’YYYY’,Right(Rtrim(cast(10000+Year(@Date) as Char(5))),4));
Select @Fmt=Replace(@Fmt,’YY’,Right(Rtrim(cast(10000+Year(@Date) as Char(6))),2));
Return ltrim(rtrim(@Fmt));
End
GO
SELECT getdate(),
dbo.Format(getDate(),’ddd dd-mmm-yyyy HH:mm:ss.ttt’),
dbo.Format(getDate(),’HH:mn’),
dbo.Format(getDate(),’fd-mmm-yyyy’),
dbo.Format(getDate(),’LD-mmm-yyyy’),
dbo.Format(GetDate(),’FYYY-FM’);
I need to get the number of hours from two date formats.. i.e i need to get number of hours spent by a staff on a project i have decidded to use start time and end time.. solution? :).
03:07:30.8570000 i got this as time and what is this “8570000” here…??
they are milliseconds
Sagar
This is Millisecond
how i can convert time into datatime which returns today’s date and time from dB.
let’s say i have time only in MY DB. 12:30 PM, when i use CONVERT(DATETIME, a.StartTime), it always return 1900-01-01 12:30:00.000. is there any way i can get today’s date.
like 2013-07-11 12:30:00:000
This is one of the methods
select dateadd(day,datediff(day,0,getdate()),CONVERT(DATETIME, ’12:30 PM’));
Hey, I am looking for sql statement to get number of minutes till to date in that year. Any help?
Try this
select datediff(minute,0,getdate()) as no_of_minutes
Fabulous, what a webpage it is! This website gives valuable information
to us, keep it up.
hi all,
wt is the difference between CAST() and CONVERT()
Both are same however CONVERT has advantage over CAST in terms of formatting date values with more options
Hi all,
Thanks to author for the great post!
I was wondering what the query would be if I wanted to extract date + hours + minutes out of datetime. I’m looking to get rid of the seconds.
Thanks very much for the help,
Tomas
when I use this command it returns the date , and the time of the time i run the query and not the recorded date and time in the date time field in the table?!
You have you use same logic on column also. Please share your table structure and sample data.
Hi, I’ve tryed to use this but it doesn’t function, it appears a message saying: “Type TIME is not a defined system type.”, also if I use the date, I need to compare with the BETWEEN in hours and it doesn’t function, but if I put the date (for example “BETWEEN ’04/04/2015′ AND ’04/05/2015′ “) the query give a result, but if I use “BETWEEN ’06:00:00.000′ AND ’20:00:00.000′ ” it doesn’t show any result, can anyone help please?
TO GET CURRENT TIME IN SQL
SELECT TO_CHAR(SYSDATE,’HH24:MI:SS’) FROM DUAL;
KESHAV, that’s not SQL Server syntax. Its Oracle.
how to split date month year in three columns
Simple
select day(getdate()) as day,month(getdate()) as month ,year(getdate()) as year
Hi I wanted to convert DateTime to CET timeZone
how to change 01:01:00 to 1hr 01 min 00sec
Hi, can you please tell how to convert ‘2017-12-04 14:30:14.410’ to ‘2017-12-04 14:30’.
I want only date and hh:mm
Can you please help