How to convert string to datetime in sql server

Question

I want to count total time in hh:mm:ss format. and I have minutes in int like (465).

I made:

CONVERT(varchar, CONVERT(datetime, cast(cast(TotalMin/60 as int) as nvarchar(50)) + ' : ' + cast(TotalMin%60 as nvarchar(50))),108) AS TotalMin

but it shows below error. Not in SQL Server but when I run code in c#:

Conversion failed when converting date and/or time from character string.

Show us your C# code to run this query and get the results from it. — Mahmoud Gamal, Dec 26 '13 at 07:45

Szymon · Accepted Answer · 2013-12-26T08:07:47.083

1

You can use this code to get the string in SQL Server. It will convert hours and minutes and add 00 for seconds as you don't have them (you're using integer value for minutes):

declare @min int = 465

select right('0' + cast(@min/60 as varchar(2)),2)
    + ':'
    + right('0' + cast((@min - (@min/60)*60) as varchar(2)),2)
    + ':00'

It will work for up to 5999 minutes (99 hours and 59 minutes).

If you need a Unicode version:

declare @min int = 465

select right(N'0' + cast(@min/60 as nvarchar(2)),2)
    + N':'
    + right(N'0' + cast((@min - (@min/60)*60) as nvarchar(2)),2)
    + N':00'

edited Dec 26 '13 at 08:07

answered Dec 26 '13 at 07:45

Szymon

42,577
16
96
114

Your code working but it shows "@" in place of ":" exa: 00@56@00 – Akil M Belim Dec 26 '13 at 07:54

score 0 · Answer 2 · edited May 23 '17 at 12:11

0

Try this:

TimeSpan t = TimeSpan.FromMinutes( TotalMin);

and see this for more

edited May 23 '17 at 12:11

Community

1
1

answered Dec 26 '13 at 07:45

Masoumeh Karvar

791
1
14
32

I can not change Data type because I'm getting converting records from excel file. – Akil M Belim Dec 26 '13 at 10:10

score 0 · Answer 3 · answered Dec 26 '13 at 08:04

UPDATE MyTable SET MyDate = CONVERT(datetime, '2009/07/16 08:28:01', 120)

For a full discussion of CAST and CONVERT, including the different date formatting options, see the MSDN Library Link below:

http://msdn.microsoft.com/en-us/library/ms187928.aspx

score 0 · Answer 4 · answered Dec 26 '13 at 08:08

This will help you You want to multiply out to milliseconds as the fractional part is discarded.

SELECT DATEADD(ms, 121.25 * 1000, 0)

If you want it without the date portion you can use CONVERT, with style 114

SELECT CONVERT(VARCHAR, DATEADD(ms, 121.25 * 1000, 0), 114)

How to convert string to datetime in sql server

4 Answers4