March 26, 2014 at 8:10 am
I have two dates '2014-25-03 01:02:03' & '2014-26-03 01:02:03' (yyyyddmm).
I need the difference between two dates in hh:mm:ss format.
I tried following query
[font="Arial Narrow"]declare @mindate datetime='2014-03-25 01:02:03'
declare @maxdate datetime='2014-03-26 02:03:04'
select cast(
(cast(cast(@maxDate as float) - cast(@minDate as float) as int) * 24) /* hours over 24 */
+ datepart(hh, @maxDate - @minDate) /* hours */
as varchar(10))
+ ':' + right('0' + cast(datepart(mi, @maxDate - @minDate) as varchar(2)), 2) /* minutes */
+ ':' + right('0' + cast(datepart(ss, @maxDate - @minDate) as varchar(2)), 2) /* seconds */[/font]
This works fine but as you can see the date i have used in the query is in yyyymmdd format.
The query does not work with yyyyddmm format
The expected answer is =25:01:01.
can someone help?
March 26, 2014 at 8:29 am
The format "yyyy-dd-mm" will not be accepted as a datetime datatype. You have to provide the values in this format as a string.
Next it is a rebuilding of the string to a correct datetime format and then you can use your own code:
declare @mindate_string char(19)='2014-25-03 01:02:03'
declare @maxdate_string char(19)='2014-26-03 02:03:04'
declare @mindate datetime
declare @maxdate datetime
-- rebuild the "yyyy-dd-mm" string to a datetime datatype
select @mindate = CONVERT(datetime, SUBSTRING(@mindate_string, 1, 4)-- yyyy
+ SUBSTRING(@mindate_string, 9, 2)-- mm
+ SUBSTRING(@mindate_string, 6, 2)-- dd
+ SUBSTRING(@mindate_string, 11, 9)-- hh:mm:ss
)
, @maxdate = CONVERT(datetime, SUBSTRING(@maxdate_string, 1, 4)-- yyyy
+ SUBSTRING(@maxdate_string, 9, 2)-- mm
+ SUBSTRING(@maxdate_string, 6, 2)-- dd
+ SUBSTRING(@maxdate_string, 11, 9)-- hh:mm:ss
)
-- use your own (unchanged) script to calculate the time difference
select cast(
(cast(
cast(@maxDate as float) - cast(@minDate as float) as int) * 24) /* hours over 24 */
+ datepart(hh, @maxDate - @minDate) /* hours */
as varchar(10))
+ ':' + right('0' + cast(datepart(mi, @maxDate - @minDate) as varchar(2)), 2) /* minutes */
+ ':' + right('0' + cast(datepart(ss, @maxDate - @minDate) as varchar(2)), 2) /* seconds */
Btw: because with the code I provided you first split the string to change it to a datetime datatype, you probably can use this splitting directly in your own code.... (instead of building a datetime datatype first).
March 26, 2014 at 8:36 am
You can do this.
SET DATEFORMAT YDM
declare @mindate datetime='2014-25-03 01:02:03'
declare @maxdate datetime='2014-26-03 02:03:04'
select cast(
(cast(cast(@maxDate as float) - cast(@minDate as float) as int) * 24) /* hours over 24 */
+ datepart(hh, @maxDate - @minDate) /* hours */
as varchar(10))
+ ':' + right('0' + cast(datepart(mi, @maxDate - @minDate) as varchar(2)), 2) /* minutes */
+ ':' + right('0' + cast(datepart(ss, @maxDate - @minDate) as varchar(2)), 2) /* seconds */
But you would probably be better off picking a format as your standard and sticking to that rather than try to accommodate both YDM and YMD formats. I good standard to use could be ISO 8601.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 26, 2014 at 8:48 am
This needed some changes ,but worked fine. Thanks!!
March 26, 2014 at 8:53 am
You're welcome.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 26, 2014 at 4:23 pm
Using Jason's setup code for this problem, here's a way to simplify the code a bit...
SELECT CAST(DATEDIFF(hh,0,@MaxDate-@MinDate) AS VARCHAR(10))
+RIGHT(CONVERT(CHAR(8),@MaxDate-@MinDate,108),6);
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply