Geek Answers Handbook

TSQL Get partial string of DateTime value?

I have the following TSQL query to get a different date time value:

SELECT CONVERT(DATETIME, EndDtField - StartDtField, 120) AS Duration FROM myTable;

The result will be:

  Duration 1900-01-01 23:02:04.000 ....

I tried to get a partial row from a result similar to this 01 23:02:04.000 using the RIGHT() function (actually it is preferable in the format 01 23:02:04 without 000 ):

 SELECT RIGHT(CONVERT(DATETIME, EndDtField - StartDtField, 120), 15) AS Duration FROM myTable;

Result:

  Duration 1 1900 11:02PM ...

It appears that the value of RIGHT(..) is a datetime value. Even I specify the datetime value in the format yyyy-mm-dd hh:mi:ss , but still I can not get the expected result. How can I get a partial day and time string ( dd hh:mi:ss ) from Duration? I really don't like parsing the values ​​of day , hour , minute and second , and then building the string.

Any other alternatives?

+4
source share
7 answers

You probably need the following T-SQL:

 SELECT RIGHT(CONVERT(VARCHAR, EndDtField - StartDtField, 121), 15) AS Duration FROM myTable;
+4
source

There are SQL functions that do something similar to what you are trying:

DatePart will receive a specific portion of the date. Therefore, you can use this with the "Duration" field to format it as you like.

DateDiff will get the difference between the two dates. You can replace all the subtraction with this.

+3
source

It appears that the value of RIGHT (..) is a datetime value.

No. The CONVERT value is a datetime value, because that is what you told him to convert. So your RIGHT () call does an implicit conversion to a string where the sql server gets a selection of whatever conversion format it wants (hint - by default it looks like this: mon dd yyyy hh:miAM (or PM) ). Instead, you want to convert it to varchar.

So again. You are doing it now:

  • Subtracting two dates (that's fine, but using DATEDIFF () would be better)
  • A CONVERT () call to create a datetime from this subtraction
  • Calling RIGHT () for this datetime value, which forces the sql server to do the default conversion to varchar

You want to do this:

  • Convert to a specific string type before calling RIGHT ()

or

  • Call DateDiff () up using seconds for the datepart parameter
  • Convert returned seconds to beautiful time in your client code.
+1
source

At the top of my head, the easiest way to do this is to do your calculation twice and then combine the two lines. This can be done in a single select statement.

 -- example select cast(day(getdate()) as varchar) + ' ' + convert(varchar, getdate(), 108) -- your code SELECT cast(day(EndDtField - StartDtField) as varchar) + ' ' + convert(varchar, EndDtField - StartDtField, 108) AS Duration FROM myTable;
+1
source

Try converting to varchar () before applying RIGHT ()

0
source

try it

 SELECT CAST(DATEPART(DAY,(EndDtField - StartDtField)) AS VARCHAR)+' '+CAST(DATEPART(HOUR,(EndDtField - StartDtField)) AS VARCHAR)+':'+CAST(DATEPART(MINUTE,(EndDtField - StartDtField)) AS VARCHAR)+':'+CAST(DATEPART(SECOND,(EndDtField - StartDtField)) AS VARCHAR) FROM myTable;

Bye

0
source 
 Convert(DateTime,Convert(char,GetDate(),101),101)
0
source

More articles:


All Articles