-- Datenbank: SQL

-- Kreis Lippe, Jörg Burzeja
-- ohne Gewähr --


CREATE View LIP_Datumswerte AS 

SELECT 

day (getdate ()) 
AS akt_Tag, 

month (getdate ()) 
AS akt_Monat, 

year (getdate ()) 
AS akt_Jahr, 

convert (char, getdate (),104) 
AS akt_Datum, 

CAST('31.12.' + CAST(YEAR(GETDATE())-1 AS CHAR(4)) AS DATETIME) 
AS letzteTag_Vorjahr, 

CONVERT(DATETIME,FLOOR(CONVERT(FLOAT,DATEADD(DD, -DAY(GETDATE()), GETDATE())))) 
AS letzteTag_Vormonat, 

CURRENT_TIMESTAMP [Datetime], 

CONVERT(char(10), CURRENT_TIMESTAMP, 104) [Time_entfernt], 

CAST(CONVERT(char(10), CURRENT_TIMESTAMP, 104) AS datetime) AS [Datetime_neu_erstellen], 

CONVERT(char(4), CURRENT_TIMESTAMP, 112)AS [aktuelles_Jahr], 

SUBSTRING (CONVERT(char(8), CURRENT_TIMESTAMP, 112), 5, 2) AS [aktueller_Monat], 

SUBSTRING (CONVERT(char(8), CURRENT_TIMESTAMP, 112), 7, 2) AS [aktueller_Tag], 

CONVERT (char (10), CAST (CONVERT(CHAR(6),DATEADD(MONTH, -1, GETDATE()),112) + '01' AS Datetime), 104) AS [erste_Tag_Vormonat],

CONVERT(CHAR(12), DATEADD(MONTH,DATEDIFF(MONTH,30,GETDATE()), -1), 104) AS [letzte_Tag_Vormonat], 

CONVERT (char (10), CAST (CONVERT(CHAR(6),DATEADD(MONTH, 1, GETDATE()),112) + '01' AS Datetime), 104) AS [erste_Tag_naechste_Monat],

CONVERT(CHAR(10),DATEADD(day, -1, CONVERT(CHAR(6),DATEADD(MONTH, 2, GETDATE()),112) + '01'),104) AS [letzte_Tag_naechste_Monat],

CONVERT(CHAR (10), CAST(CAST(YEAR(Getdate () ) AS CHAR(4))+'0101' AS DATETIME),104) AS [Jahresbeginn], 

CONVERT(CHAR (10), CAST(CAST(YEAR(Getdate () )-1 AS CHAR(4))+'0101' AS DATETIME),104) AS [erste_Tag_Vorjahr], 

CONVERT(CHAR (10), CAST(CAST((YEAR(Getdate () ) -1) AS CHAR(4))+'1231' AS DATETIME),104) AS [letzte_Tag_Vorjahr], 

CONVERT (CHAR (10), CAST(CAST(YEAR(getdate ()) AS CHAR(4))+'-12-31T23:59:59.997' AS DATETIME),104) AS Jahresende, 

DATEDIFF(DAY, CAST(CAST(YEAR(Getdate () ) AS CHAR(4))+'0101' AS DATETIME), GETDATE()) AS [Tage_bis_heute], 

DATEDIFF(MONTH, CAST(CAST(YEAR(Getdate () ) AS CHAR(4))+'0101' AS DATETIME), GETDATE()) AS [Monate_bis_heute], 

CONVERT(CHAR (10), CAST(CAST(YEAR(Getdate () ) AS CHAR(4))+'0413' AS DATETIME),104) AS [bestimmter_Tag_aktuelles_Jahr]