Convert number into year, month, day, hour, minute and second 1


We have an integer number which represents seconds (secs) part of the time. We need to convert this integer number into year, month, days, hour, minute and second parts. Let me explain with the help of the examples to make it clear.

Examples:

100 Seconds – 0 year(s) 0 month(s) 0 day(s) 0 hour(s) 1 minute(s) 40 second(s)

3600 Seconds – 0 year(s) 0 month(s) 0 day(s) 1 hour(s) 0 minute(s) 0 second(s)

86400 Seconds – 0 year(s) 0 month(s) 1 day(s) 0 hour(s) 0 minute(s) 0 second(s)

2678400 Seconds – 0 year(s) 1 month(s) 0 day(s) 0 hour(s) 0 minute(s) 0 second(s)

35110011 Seconds – 1 year(s) 1 month(s) 10 day(s) 8 hour(s) 46 minute(s) 51 second(s)

etc.

Convert number into year, month, day, hour, minute and second

Below is the code to convert the input integer number into year, month, day, hour, minute and second as above:

DECLARE @VARDT DATETIME = DATEADD(SECOND, 35110011, 0)
SELECT CAST(DATEPART(YEAR, @VARDT) - 1900 AS VARCHAR(10)) + ' year(s) ' + CAST(DATEPART(MONTH, @VARDT) - 1 AS VARCHAR(2)) + ' month(s) '
+ CAST(DATEPART(DD, @VARDT) - 1 AS VARCHAR(2)) + ' day(s) ' + CAST(DATEPART(HOUR, @VARDT) AS VARCHAR(2)) + ' hour(s) '
+ CAST(DATEPART(MINUTE, @VARDT) AS VARCHAR(2)) + ' minute(s) ' + CAST(DATEPART(SECOND, @VARDT) AS VARCHAR(2)) + ' second(s)'

Or, we can use the below code.

DECLARE @VARDT DATETIME = DATEADD(SECOND, 35110011, 0)
SELECT CAST(DATEDIFF(YEAR, 0, @VARDT) AS VARCHAR(10)) + ' year(s) ' + CAST(DATEPART(MONTH, @VARDT) - 1 AS VARCHAR(2)) + ' month(s) '
 + CAST(DATEPART(DD, @VARDT) - 1 AS VARCHAR(2)) + ' day(s) ' + CAST(DATEPART(HOUR, @VARDT) AS VARCHAR(2)) + ' hour(s) '
 + CAST(DATEPART(MINUTE, @VARDT) AS VARCHAR(2)) + ' minute(s) ' + CAST(DATEPART(SECOND, @VARDT) AS VARCHAR(2)) + ' second(s)'

Output:

1 year(s) 1 month(s) 10 day(s) 8 hour(s) 46 minute(s) 51 second(s)

I have added the given integer value in second part of the default datetime value (‘1900-01-01 00:00:00.000’) of SQL Server to generate a datetime value. Once we have a datetime data type, we can easily apply the DATEPART function to extract the required parts from that. In this scenario SQL Server automatically takes care of the date time conversions. Finally, I have subtracted the respective date time parts from the generated datetime data type value from their respective parts in the final SELECT statement wherever required.

In second logic, I have just used a DATEDIFF function for the first step computation which can take care of all the conversions specially in below scenarios smoothly. For example, we don’t need to worry about to year to month, month to day and day to hour conversion. We just need to use DATEDIFF function for the first part.

Convert number into month, day, hour, minute and second

SELECT CAST((DATEDIFF(MONTH, 0, @VARDT)) AS VARCHAR(10)) + ' month(s) '
+ CAST(DATEPART(DD, @VARDT) - 1 AS VARCHAR(2)) + ' day(s) ' + CAST(DATEPART(HOUR, @VARDT) AS VARCHAR(2)) + ' hour(s) '
+ CAST(DATEPART(MINUTE, @VARDT) AS VARCHAR(2)) + ' minute(s) ' + CAST(DATEPART(SECOND, @VARDT) AS VARCHAR(2)) + ' second(s)'

Output:

13 month(s) 10 day(s) 8 hour(s) 46 minute(s) 51 second(s)

Convert number into day, hour, minute and second

SELECT CAST((DATEDIFF(DD, 0, @VARDT)) AS VARCHAR(10)) + ' day(s) ' 
 + CAST(DATEPART(HOUR, @VARDT) AS VARCHAR(2)) + ' hour(s) '
 + CAST(DATEPART(MINUTE, @VARDT) AS VARCHAR(2)) + ' minute(s) ' + CAST(DATEPART(SECOND, @VARDT) AS VARCHAR(2)) + ' second(s)'

Output:

406 day(s) 8 hour(s) 46 minute(s) 51 second(s)

Convert number into hour, minute and second

SELECT CAST(DATEDIFF(HOUR, 0, @VARDT) AS VARCHAR(20)) + ' hour(s) '
 + CAST(DATEPART(MINUTE, @VARDT) AS VARCHAR(2)) + ' minute(s) ' + CAST(DATEPART(SECOND, @VARDT) AS VARCHAR(2)) + ' second(s)'

Output:

9752 hour(s) 46 minute(s) 51 second(s)

Convert number into minute and second

SELECT CAST(DATEDIFF(MINUTE, 0, @VARDT) AS VARCHAR(25)) + ' minute(s) ' + CAST(DATEPART(SECOND, @VARDT) AS VARCHAR(2)) + ' second(s)'

Output:

585166 minute(s) 51 second(s)

Indeed, we could achieve this in many ways, I have shared one of them. You can create and share your method below in the comment box. Thank you for the reading.

Please do share and like this post.

Rate This
[Total: 4    Average: 4.8/5]

Gopal Krishna Ranjan

About Gopal Krishna Ranjan

Gopal has 8 years of industry experience in Software development. He has a head down experience in Data Science, Database, Data Warehouse, Big Data and cloud technologies and has implemented end to end solutions. He has extensively worked on SQL Server, Python, Hadoop, Hive, Spark, Azure, Machine Learning, and MSBI (SSAS, SSIS, and SSRS). He also has good experience in windows and web application development using ASP.Net and C#.


Leave a comment

Your email address will not be published. Required fields are marked *

One thought on “Convert number into year, month, day, hour, minute and second

  • ani

    hi Gopal Krishna Ranjan,
    This articl is very useful and helped me a lot.
    Thanks for article
    i have a query. can you help me for solution?
    its regarding datetime in SSRS reports.
    How can I fetch records based on minute and seconds in datetime field in SSRS? Say I have dateandtime column in my sql server table as
    2017-06-12 22:17:14.000
    2017-06-12 22:17:16.000
    2017-06-12 22:17:19.000
    2017-06-12 22:17:21.000
    In which data is dumped automatically after n seconds, lets say after every 2 seconds.
    My req is that I need to select those records only which are not dumped after every 2 seconds (means which are dumped after every != 2 (1 or 3 or 5 or n) seonds) while putting 2 (second) in textbox parameter in SSRS report designer.
    Any solution will be appreciated. Anybody?
    i need to filter this because this report has very large pages, more than 100, as data is dumping after every 2 seconds or n seconds for same day/date.
    Thanks