MS SQL - generovanie rôznych dátumov

Zdravím databázových programátorov. Dnes si ukážeme zopár malých príkladov, ako vygenerovať dátumy a časy, upravovať a formátovať ich a počítať s nimi rôzne operácie. Tieto SQL dotazy sú generované pomocou SQL príkazov bez pomoci čítania dát z tabuľky.

 Príklady:

 

-- aktuálny dátum a cas ...
SELECT 
	GETDATE();

-- vyberieme cast z dátumu a casu ...
SELECT 
	GETDATE() AS "Aktualny datum a cas", 
	DATEPART(DAY, GETDATE()) AS "Day", 
	DATEPART(MONTH, GETDATE()) AS "Month", 
	DATEPART(YEAR, GETDATE()) AS "Year", 
	DATEPART(HOUR, GETDATE()) AS "Hour part 1.", DATEPART(hh,GETDATE()) 'Hour part 2.',
	DATEPART(MINUTE, GETDATE()) AS "Minute part 1.", DATEPART(mi,GETDATE()) 'Minute part 2.', DATEPART(n,GETDATE()) 'Minute part 3.',
	DATEPART(SECOND, GETDATE()) AS "Second part 1.", DATEPART(SS, GETDATE()) AS "Second part 2.", DATEPART(S, GETDATE()) AS "Second part 3.",
	DATEPART(millisecond,GETDATE()) 'MilliSecond Part 1.', DATEPART(ms,GETDATE()) 'MilliSecond Part 2.'

-- formátovanie dátumu a casu ...
SELECT 
	GETDATE() 'Today',
	CAST(SYSDATETIME() AS TIME) 'hh:mm:ss.nnnnnnn',
	CONVERT(VARCHAR(5), GETDATE(), 108) + (CASE WHEN DATEPART(HOUR, GETDATE()) > 12 THEN 'PM' ELSE 'AM' END) 'hh:mi AM/PM format',
	LTRIM(RIGHT(CONVERT(VARCHAR(20), GETDATE(), 100), 7)) 'hh:mi AM/PM format',
	RIGHT(CONVERT(VARCHAR(26), GETDATE(), 109),14) 'hh:mi:ss:mmmAM (or PM)',
	CONVERT(VARCHAR(12),GETDATE(),114) 'hh:mi:ss:mmm',
	CONVERT(VARCHAR(8), GETDATE(), 108) 'hh:mi:ss'
;

-- rozdiely dátumov a casov ...
SELECT DATEDIFF(year, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');
SELECT DATEDIFF(quarter, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');
SELECT DATEDIFF(month, '2005-12-31 23:59:59.9999999', '2006-03-01 00:00:00.0000000');
SELECT DATEDIFF(dayofyear, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');
SELECT DATEDIFF(day, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');
SELECT DATEDIFF(week, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');
SELECT DATEDIFF(hour, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');
SELECT DATEDIFF(minute, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');
SELECT DATEDIFF(second, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');
SELECT DATEDIFF(millisecond, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');

-- rôzne hodnoty a názvy z dátumu a casu ...
SELECT 
	DATENAME(year, GETDATE()) AS 'Year',
	DATENAME(month, GETDATE()) AS "Month name",
	DATENAME(day, GETDATE()) AS "Day",
	DATENAME(dayofyear, GETDATE()) AS "Day rank",
	DATENAME(quarter, GETDATE()) AS "Quarter of year",
	DATENAME(week, GETDATE()) AS "Week number of year",
	DATENAME(weekday, GETDATE()) AS "Day name"
;  

-- posúvanie dátumu a casu ...
SELECT 
	DATEADD(MINUTE, -40, GETDATE()) AS "Datum a cas pred 40 minutami",
	DATEADD(DAY, +7, GETDATE()) AS "Datum a cas o tyzden"
;

DECLARE @dt datetime2;  
SET @dt = GETDATE();  
SELECT 
	DATEADD(quarter,4,@dt) AS "Posun o 3/4 roku dopredu",
	DATEADD(month,13,@dt) AS "Posun o 13 mesiacov dopredu",  
	DATEADD(day,365,@dt) AS "Posun o 365 dni dopredu",  
	DATEADD(week,5,@dt)  AS "Posun o 5 tyzdnov dopredu",  
	DATEADD(weekday, -31,@dt) AS "Posun o 31 dni dozadu",  
	DATEADD(hour,23,@dt) AS "Posun o 23 hodin dopredu",
	DATEADD(minute,59,@dt) AS "Posun o 59 minut dopredu",
	DATEADD(second,-59,@dt) AS "Posun o 59 sekund dozadu"
;

-- zoskupovanie dátumu a casu do intervalov ...
SELECT 
	GETDATE() AS "Aktualny datum cas",
	DATEADD(MINUTE, DATEDIFF(MINUTE,0,GETDATE()) / 5 * 5, 0) AS "5min",
	DATEADD(MINUTE, DATEDIFF(MINUTE,0,GETDATE()) / 15 * 15, 0) AS "Stvrthodina",
	DATEADD(MINUTE, DATEDIFF(MINUTE,0,GETDATE()) / 30 * 30, 0) AS "Polhodina",
	DATEADD(MINUTE, DATEDIFF(MINUTE,0,GETDATE()) / 60 * 60, 0) AS "Hodina"	
;

-- praca/posúvanie s dátumu a casu trochu inak bez použitia funkcií ...
SELECT 
	GETDATE() AS "Aktualny datum cas",
	GETDATE()+1.0/(24*60*60) AS "Datum a cas o sekundu dopredu",	
	GETDATE()+1.0/(24*60) AS "Datum a cas o minutu dopredu",	
	GETDATE()-15.0/(24*60) AS "Datum a cas pred 15 minutami",		
	GETDATE()+15.0/(24*60) AS "Datum a cas o 15 minut dopredu",		
	GETDATE()+1.0/24 AS "Datum a cas o hodinu dopredu",	
	GETDATE()+1.0 AS "Zajtra o takomto case",
	GETDATE()+7.0 AS "O tyzden o takomto case"
;

 

 

Dúfam, že tieto zdrojové SQL kódy pomôžu mnohým programátorom a ušetria veľa času pri vymýšlaní a programovaní.

S pozdravom.

Alojz Bendák.

O autorovi

Alojz Benďák

Autor je administrátor webu a venuje sa programovaniu takmer 23 rokov. prvý kontakt s počítačmi na strednej škole - PMD 1,2,3, PP06, Atari, Sinclair (programovanie v jazyku: strojový kód, basic a pascal): 2 roky na VŠ - jazyky Turbo pascal a Turbo C: 1rok programovanie databázového ekonomického širokoškálneho software…

Súvisiace články