Convert my date column like "2015-08-24 12:05:19.000" into just date and minute values

Question

SQL Server query to convert my date column like 2015-08-24 12:05:19.000 into just date and minute values.

For example 2015-08-24 12:05:19.000 should be converted into 2015-08-24 0.0833 which is (5/60=0.083) hours . ie 12.05 is 5 minutes past 12 o clock and 5/60 fraction of that hour.

Any suggestions or please help with query. Convert or any other normal date functions didn't help me. Please note this is single date column having date in rows. I don't want date difference between multiple columns.

The date time is stored as it is - you can't control the format it is stored in the db. So is this just a formatting question? — Jason W, Dec 19 '16 at 04:20
Its not just formatting, i need to use the data in hours for some report. hence i need it in hours. I have millions of data in the same column in that format, each having 10 secs to one min difference. — Nischey D, Dec 19 '16 at 04:56

score 1 · Accepted Answer · answered Dec 19 '16 at 18:54

You might consider using DATEDIFF with the TIME portion only and leverage the formatting options within CONVERT to make a simpler output. Since this might be used all over in reporting as you suggested, perhaps wrap it into a scalar function as well (then use that in report queries or even as calculated fields).

CREATE FUNCTION dbo.f_GetDateValue(
    @Date DATETIME,
    @Digits INT = 4 -- How many digits of precision?
) RETURNS VARCHAR(50) AS
BEGIN

    -- Calculate decimal to millisecond level
    DECLARE @Decimal NUMERIC(9,0) = ROUND(CONVERT(NUMERIC(9,0),
        DATEDIFF(MS, 0, CONVERT(TIME, @Date)))/86400000, @Digits)

    -- Return formatted date and time as decimal
    RETURN CONVERT(CHAR(10), @Date, 121) -- Format date as yyyy-MM-dd
        + ' ' -- Separate date and time value
        + LEFT(CONVERT(VARCHAR(50), @Decimal), @Digits + 2) -- 2 for "0." and rounded @Digits
END
GO

With scalar function above, your query is then just:

SELECT dbo.f_GetDateValue(GETDATE(), DEFAULT) -- Gets "yyyy-MM-dd 0.0000"
SELECT dbo.f_GetDateValue(GETDATE(), 2) -- Gets "yyyy-MM-dd 0.00"

As noted in comments and other answers, you cannot change how SQL is storing date values. This creates a text-based representation only that is only as sortable as the format of the text. Fortunately, since you are using YYYY-MM-DD 0.0000, this should be sortable in most reports as the text itself.

Hadi · Answer 2 · 2016-12-19T19:12:27.613

0

You can't control the way that sql store your datetime column but you can get your own format from a SELECT query:

CONVERT(CHAR(10), [DATECOLUMN], 121)  + '  ' + 
       CAST(CAST((DATEPART(MINUTE,[DATECOLUMN]) as float)/ Cast(60 as float)) a varchar(5)) 
FROM MYTABLE

If you want to show month and day with 2 characters like 01 even if they are smaller than 10 you can use

CONVERT(CHAR(10), [DATECOLUMN], 121)  + '  ' + 
       CAST(CAST((DATEPART(MINUTE,[DATECOLUMN]) as float)/ Cast(60 as float)) a varchar(5)) 
FROM MYTABLE

YEAR() MONTH() DAY() DATEPART() functions can be used to get date parts

edited Dec 19 '16 at 19:12

answered Dec 19 '16 at 04:28

Hadi

36,233
13
65
124

Hi Hadi, Thanks for query , but the divide by 60 gives me only 0 as output. ie "2015-08-24 12:05:19.000" turns into "2015:8:24 0" – Nischey D Dec 19 '16 at 05:03
you can check this link and read the answer and its comments http://stackoverflow.com/questions/11719044/how-to-get-a-float-result-by-dividing-two-integer-values – Hadi Dec 19 '16 at 05:05
1

It might be better using `CONVERT(CHAR(10), [DATECOLUMN], 121)` instead of 3 separate parsing commands or manually padding the `0` for month and day. – Jason W Dec 19 '16 at 18:57

score 0 · Answer 3 · answered Dec 19 '16 at 05:21

DECLARE @d datetime='2015-08-24 12:05:19.000'
SELECT DATEDIFF(MINUTE,DATEADD(HOUR,12,CONVERT(VARCHAR,@d,110)),@d)/60.0

SELECT CONVERT(VARCHAR,@d,23)+' '+CONVERT(VARCHAR, DATEDIFF(MINUTE,DATEADD(HOUR,12,CONVERT(VARCHAR,@d,110)),@d)/60.0)

--2015-08-24 0.083333

score 0 · Answer 4 · answered Dec 19 '16 at 06:43

0

This code works perfectly assuming user needs to get the only fraction of minutes for that hour only.For reference go through the snapshot of output

select Cast(Convert(Date,'2015-08-24 12:05:19.000') AS nvarchar)
+' '+Cast(DATEPART(hour,'2015-08-24 12:05:19.000') AS nvarchar)
+' '+Cast(Convert(float,(datepart(MINUTE,'2015-08-24 12:05:19.000')))/60 
AS nvarchar)

answered Dec 19 '16 at 06:43

vinay koul

348
1
9

Hello all, thanks for help. I managed changing the data at the analysis level ie in Spotfire application . By dividing the data by 60 while displaying . However All the shared solution seems pretty usefull in converting the date format in different ways . This will be good KT for us. – Nischey D Dec 22 '16 at 08:19

Convert my date column like "2015-08-24 12:05:19.000" into just date and minute values

4 Answers4