Diskuze: Substring - rozdělení pole na tři části
Zobrazeno 4 zpráv z 4.
//= Settings::TRACKING_CODE_B ?> //= Settings::TRACKING_CODE ?>
Jaký je to databázový server? Ono se to dost liší
Zkus míst to_char .... použít FORMAT(VALUE1,'dd.mm.yyyy')
na MS SQL je to FORMAT(VALUE1,'dd.MM.yyyy') pro čas je to stejné
jen jako druhý parametr použiješ např. na ORACLE 'hh24:mi'
Pokud je to oracle nebo MYSQL mělo by jít i jednoduše toto
select to_char(SLOUPEC,'dd.mm.yyyy')
to_char(SLOUPEC,' HH24:mi') from TABULKA
VALUE1(DATE) = 21.10.19 11:30:45, 727066000
VALUE2(DATE) = 21.10.19 11:45:24, 354907000
Jestli to spravne chapu, mas 2 sloupce DATE a potrebujes ziskat rozdil obou
datumu a vysledek preformatovat na hodiny:minuty:sekundy. Co treba to napsat
do zadani?
google = sql date sub format h:m:s
google = sql date difference format h:m:s
DATE_FORMAT(cislo, '%H:%i:%S')
DATEDIFF(second, date1, date2)
select @StartDate = '10/01/2012 08:40:18.000',@EndDate='10/04/2012 09:52:48.000'
select convert(varchar(5),DateDiff(s, @startDate, @EndDate)/3600)+':'+convert(varchar(5),DateDiff(s, @startDate, @EndDate)%3600/60)+':'+convert(varchar(5),(DateDiff(s, @startDate, @EndDate)%60)) as [hh:mm:ss]
Select CAST((@EndDateTime-@StartDateTime) as time(0)) '[hh:mm:ss]'
CREATE FUNCTION getDateDiff(@startDate DATETIME, @endDate DATETIME)
RETURNS VARCHAR(10)
AS BEGIN
DECLARE @seconds INT = DATEDIFF(s, @startDate, @endDate)
DECLARE @difference VARCHAR(10) =
CONVERT(VARCHAR(4), @seconds / 3600) + ':' +
CONVERT(VARCHAR(2), @seconds % 3600 / 60) + ':' +
CONVERT(VARCHAR(2), @seconds % 60)
RETURN @difference
END
DECLARE @StartDate datetime = '10/01/2012 08:40:18.000'
,@EndDate datetime = '10/04/2012 09:52:48.000'
SELECT
STR(ss/3600, 5) + ':' + RIGHT('0' + LTRIM(ss%3600/60), 2) + ':' + RIGHT('0' + LTRIM(ss%60), 2) AS [hh:mm:ss]
FROM (VALUES(DATEDIFF(s, @StartDate, @EndDate))) seconds (ss)
DECLARE @dt1 datetime='2012/06/13 08:11:12', @dt2 datetime='2012/06/12 02:11:12'
SELECT CAST((@dt2-@dt1) as time(0))
https://docs.microsoft.com/…transact-sql?…
-- DOES NOT ACCOUNT FOR LEAP YEARS
DECLARE @date1 DATETIME, @date2 DATETIME, @result VARCHAR(100);
DECLARE @years INT, @months INT, @days INT,
@hours INT, @minutes INT, @seconds INT, @milliseconds INT;
SET @date1 = '1900-01-01 00:00:00.000'
SET @date2 = '2018-12-12 07:08:01.123'
SELECT @years = DATEDIFF(yy, @date1, @date2)
IF DATEADD(yy, -@years, @date2) < @date1
SELECT @years = @years-1
SET @date2 = DATEADD(yy, -@years, @date2)
SELECT @months = DATEDIFF(mm, @date1, @date2)
IF DATEADD(mm, -@months, @date2) < @date1
SELECT @months=@months-1
SET @date2= DATEADD(mm, -@months, @date2)
SELECT @days=DATEDIFF(dd, @date1, @date2)
IF DATEADD(dd, -@days, @date2) < @date1
SELECT @days=@days-1
SET @date2= DATEADD(dd, -@days, @date2)
SELECT @hours=DATEDIFF(hh, @date1, @date2)
IF DATEADD(hh, -@hours, @date2) < @date1
SELECT @hours=@hours-1
SET @date2= DATEADD(hh, -@hours, @date2)
SELECT @minutes=DATEDIFF(mi, @date1, @date2)
IF DATEADD(mi, -@minutes, @date2) < @date1
SELECT @minutes=@minutes-1
SET @date2= DATEADD(mi, -@minutes, @date2)
SELECT @seconds=DATEDIFF(s, @date1, @date2)
IF DATEADD(s, -@seconds, @date2) < @date1
SELECT @seconds=@seconds-1
SET @date2= DATEADD(s, -@seconds, @date2)
SELECT @milliseconds=DATEDIFF(ms, @date1, @date2)
SELECT @result= ISNULL(CAST(NULLIF(@years,0) AS VARCHAR(10)) + ' years,','')
+ ISNULL(' ' + CAST(NULLIF(@months,0) AS VARCHAR(10)) + ' months,','')
+ ISNULL(' ' + CAST(NULLIF(@days,0) AS VARCHAR(10)) + ' days,','')
+ ISNULL(' ' + CAST(NULLIF(@hours,0) AS VARCHAR(10)) + ' hours,','')
+ ISNULL(' ' + CAST(@minutes AS VARCHAR(10)) + ' minutes and','')
+ ISNULL(' ' + CAST(@seconds AS VARCHAR(10))
+ CASE
WHEN @milliseconds > 0
THEN '.' + CAST(@milliseconds AS VARCHAR(10))
ELSE ''
END
+ ' seconds','')
SELECT @result
-- 118 years, 11 months, 11 days, 7 hours, 8 minutes and 1.123 seconds
Tam je problem ten, ze to musis rozdil pocitat podle realneho casu, ktery ubehl mezi temi obdobimi. Nelze to zjednodusit na rozdil hodin, minut, sekund, protoze rok ma 365.25+- dni a kazdy mesic ma jiny pocet mesicu. Takze, kdyz ten rozdil presahne jeden den, tak uz nastava problem. Pokud nepresahne, tak muzes pouzit date format. On ti vygeneruje datum pro rok 1970, ale to te nezajima, pocet hodinu bude odpovidat.
Zobrazeno 4 zpráv z 4.