Hi @Rahul Polaboina
Here is sample SELECT script:
SELECT FORMAT(cast('17:00' as datetime),'hh:mm:tt');
SELECT FORMAT(cast('14:00' as datetime),'hh:mm:tt');
Converting varchar time HHMM to 24 hour HH:MM:SS? - SQL Server Forums
sql server 2012 - Converting varchar(6) in HHmmss format to sql time format - Stack Overflow
sql - TSQL Convert VARCHAR of HHMMSS to TIME - Stack Overflow
How to convert hhmm into datetime 13:30:00 T-SQL? – SQLServerCentral Forums
One option is use to use Format()
Example
Declare @YourTable table (SomeCol int)
Insert into @YourTable values
(2130)
,(103)
,(10)
,(1)
Select SomeCol
,TimeValue = format(SomeCol,'00:00')
From @YourTable
Returns
SomeCol TimeValue
2130 21:30
103 01:03
10 00:10
1 00:01
EDIT - Requested EDIT for 2008
Declare @YourTable table (SomeCol int)
Insert into @YourTable values
(2130)
,(103)
,(10)
,(1)
Select SomeCol
,TimeValue = stuff(right('0000' + left(SomeCol, 4),4), 3, 0, ':')
From @YourTable
You can try the following using length of column value and case statement.
Declare @YourTable table (SomeCol int)
Insert into @YourTable values
(2130)
,(103)
,(10)
,(1)
Select
case LEN(SomeCol)
when 4 then SUBSTRING('2130', 1, 2) + ':' + SUBSTRING('2130', 3, 2)
when 3 then '0' + SUBSTRING('103', 1, 1) + ':' + SUBSTRING('103', 2, 2)
when 2 then '00:' + CONVERT(Varchar(5), SomeCol)
else '00:0' + CONVERT(Varchar(5), SomeCol)
end as TimeValue
from @YourTable
One more approach
DECLARE @t VARCHAR(6)='180000';
SELECT CAST(STUFF(STUFF(@t,5,0,':'),3,0,':') AS time)
Thanks to @SebtHU, who pointed out, that this would not work with leading zeros in times such as 09:15:00 or 00:45:00. You can use this instead:
SELECT CAST(STUFF(STUFF(RIGHT(CONCAT('000000',@t),6),5,0,':'),3,0,':') AS time);
try this :
select cast((substring('180000',0,3)+':'+substring('180000',3,2)+':'+substring('180000',5,2)) as time)
Declare @YourTable table (TimeStr varchar(25))
Insert Into @YourTable values
('151200'),
('085800'),
('080100'),
('210100'),
('083300')
Select *
,AsTime = cast(stuff(stuff(TimeStr,5,0,':'),3,0,':') as time)
From @YourTable
Returns
TimeStr AsTime
151200 15:12:00.0000000
085800 08:58:00.0000000
080100 08:01:00.0000000
210100 21:01:00.0000000
083300 08:33:00.0000000
EDIT:
Depending on your version an considering you are storing time values in a varchar (which means you could have bogus data), you could use:
try_convert(time,stuff(stuff(TimeStr,5,0,':'),3,0,':'))
If you are using SQL Server 2012 or above and want to store the values as actual TIME data types, you can use the TIMEFROMPARTS function.
For example:
DECLARE @d VARCHAR(10)
SET @d = '151200'
SELECT TIMEFROMPARTS(LEFT(@d,2), SUBSTRING(@d,3,2), RIGHT(@d,2), 0)
This will return a TIME. For more information, please see this: https://msdn.microsoft.com/en-us/library/hh213398.aspx
The string is in the wrong format. It should be 09:47:11.895799. You need to replace the final : with a .
SELECT CAST(STUFF('09:47:11:895799', 9,1,'.') AS TIME)
You can also make your string as hh:mi:ss:mmm (24 hour format) and then convert as time. That means trimming out last 3 digits from your milli seconds without replacing last ':' with '.'
Here is MSDN link for more details of formatting. Here is working example of SQL Fiddle
DECLARE @s varchar(50) = '09:47:11:895799'
SELECT Convert(time, Left(@s,Len(@s)-3), 114)
CONVERT(VARCHAR(5),Date, 108)-- Gets only HH:mm
In general, the set of timestamps is not well-ordered, this means you cannot get a "last" timestamp whose time part up to minutes is 2009-05-06 14:58.
In SQL Server, which keeps the time part of a datetime as a number of 1/300 second fractions after midnight, this "last" timestamp would be 2009-05-06 14:58:59.997, but this is not guaranteed to be compatible with future releases of with other TIMESTAMP storage methods.
That means you'll need to split your BETWEEN condition into two conditions, one of which being strict less than the next minute:
select Count(Page) as VisitingCount,Page,CONVERT(VARCHAR(8),Date, 108) from scr_SecuristLog
where Date >= '2009-05-04 00:00:00'
AND Date < DATEADD(minute, 1, '2009-05-06 14:58')
and [user] in(select USERNAME
from scr_CustomerAuthorities )
group by Page,Date order by [VisitingCount] asc
This solution will efficiently use indexes on Date
DECLARE @InX VARCHAR(10)='09:08:23'
SELECT CAST(@InX AS TIME(0))
You can convert those values to time and for summing you could simply get their difference in seconds from midnight and sum. Result would be in seconds. ie:
DECLARE @times TABLE(t TIME(0));
INSERT INTO @times(t)VALUES('03:20:30'), ('02:10'), ('01:00');
SELECT SUM(DATEDIFF(SECOND, '00:00:00', t))FROM @times AS t;
Supported by SQL Server 2005 and later versions
SELECT CONVERT(VARCHAR(10), GETDATE(), 101)
+ ' ' + CONVERT(VARCHAR(8), GETDATE(), 108)
* See Microsoft's documentation to understand what the 101 and 108 style codes above mean.
Supported by SQL Server 2012 and later versions
SELECT FORMAT(GETDATE() , 'MM/dd/yyyy HH:mm:ss')
Result
Both of the above methods will return:
10/16/2013 17:00:20
Try below:
SELECT CONVERT(VARCHAR(20), GETDATE(), 101)