Datum, tid och funktioner

Att hantera datum och tid kan vara lite knepigt i början, men om du använder formatet 1950-03-22 fungerar det som det ska. Om du däremot använder 22/03/1950 blir det fel. Det beror på att den antar att det är den tredje dagen i månad tjugotvå som inte finns. Alltså USA-format.

Ett tvåsiffrigt årtal som är mindre än eller lika med 49, blir 2000-talet och om det är större än 49 blir 1900-talet. Alltså, 38 och 49 blir 2038 och 2049, medan 50 och 90 blir 1950 och 1990. Det bästa är dock att använda fyra siffror för årtal där det är möjligt.

Standardformat för datum bestäms av nuvarande språkinställningen. Du kan ändra datumformat genom att använda satserna SET LANGUAGE som beskrivs längre ner och SET DATEFORMAT. Det första du antagligen vill göra med ett datum är att omvandla det till ett mer läsbart format. För det använder man funktionen CONVERT som beskrivs längre ner.

Från minuter till timmar

Timmar

Om man har tabellen filmer och i den har en kolumn med längden i minuter, behöver man dela antalet minuter med 60, för att få fram antalet timmar.

SELECT
    FilmName,
    FilmMinutes / 60 AS Timmar
FROM
    Film;

Resultatet i kolumnen ”Timmar” för ett film på 132 minuter blir 2.

Ovanstående exempel visar först kolumnen med namnet på filmerna och en kolumn med det totala antalet minuter för varje film. Sedan skapas även kolumnen med alias ”Timmar” som visar antalet timmar. Eftersom jag använder mig av en heltalsdivision (båda datatyperna i uttrycket är av typen INT), blir resultatet enbart timmar. Om jag i stället skulle delat med ett decimaltal (60.0), hade svaret blivit det också.

SELECT
    FilmName,
    FilmMinutes / 60.0 AS Timmar,
FROM
    Film;

Resultatet i kolumnen ”Timmar” för en film på 132 minuter blir. 2.20.

Eftersom det är timmar vi är ute efter i den nya kolumnen, är det givetvis en heltalsdivision som vi ska använda oss av. Däremot behöver vi göra ytterligare kalkyler för att visa de minuterna som är kvar. Två timmar är 120 minuter, vilket innebär att en totallängd på 132 minuter får 12 minuter kvar.

Minuter

Man skulle därför kunna dela med 60.0 för nästa kolumn med alias ”Minuter”. Då blir resultatet 2.20 som i föregående exempel, där siffran 2 till vänster om punkten är timmar. Dessa timmar tar man sedan bort genom att subtrahera med heltalsdivisionen. Kvar blir då 0.2. För att omvandla detta decimaltal till minuter måste man sedan multiplicera med 60.

SELECT
    FilmName,
    FilmMinutes / 60 AS Timmar,
    (FilmMinutes / 60.0 - FilmMinutes / 60) * 60 AS Minuter
FROM
    Film;

Även om  det här fungerar är det en onödig väg att gå och det kan också bli en minut för lite om vi inte utför ytterligare operationer.

Timmar och minuter

Lösningen är att använda den aritmetiska operatorn modulus (%). Med den gör man en heltalsdivision och svaret blir den rest som är kvar. Så för 132 minuter blir det 12 minuter kvar efter att två timmar, alltså 120 minuter har dragits ifrån.

SELECT
    FilmName,
    FilmMinutes / 60 AS Timmar,
    FilmMinutes % 60 AS Minuter
FROM
    Film;

Funktioner för datum

Convert

CONVERT(data_type(length) , expression, style)

Funktionen Convert, som egentligen tillhör konverteringsfunktioner, används för att konvertera ett uttryck från en datatyp till en annan och den är specifik för SQL Server med utökade funktioner för att formatera datum. Första argumentet är datatypen och längden på den. Andra argumentet är uttrycket och tredje argumentet är ett heltal som specificerar hur Convert-funktionen ska översätta uttrycket.

Convert(Char(8), FilmReleaseDate, 3)

Resultatet blir 16/07/93, som är brittiskt standardformat.

Siffran 3 innebär brittiskt standardformat. För att få brittiskt standardformat med fyra siffror för året används siffran 103. Eftersom det innebär ytterligare två tecken måste Char ändras från 8 till 10.

Datename

Datename(datepart, date)

Returnerar en sträng som representerar den specificerade delen av det angivna datumet. Första argumentet är den delen av datumet som ska returneras t ex year, month, week, hour eller minute. Användardefinierade variabler är inte giltiga. Andra argumentet är ett uttryck som kan omvandlas till tid eller datum. Det kan vara ett uttryck, kolumnuttryck, användardefinierad variabel eller en sträng. För att undvika oklarheter, använd fyrsiffriga år.

SELECT
    Name,
    BirthDate,
    Datename(M, BirthDate) AS 'Månad'
FROM
    Personer;

Här skapas en ny kolumn med alias ”Månad” där personernas födelsemånad skrivs ut, t ex June.

Med Datename i stället för Convert, kan man bygga upp ett format som inte är standard.

SELECT
    Name,
    BirthDate,
    Convert(Char(10), BirthDate, 103),
    Datename(WEEKDAY,BirthDate) + ', ' +
    Datename(DAY,BirthDate) + ' ' +
    Datename(MONTH,BirthDate) + ' ' +
    Datename(YEAR,BirthDate)
FROM
    Personer;

Result för 19930510 blir ”Friday, 10 may 1993”.

Svenska namn för månader och veckodagar

När man väljer veckodag och månad i textformat returneras det engelska ordet. Hur får man dessa på svenska? Finns det någon anledning att göra detta i databasen eller är det alltid bäst att låta klientsidan sköta det här? 

I nuläget vet jag inte vilka metoder som är bäst, men ett sätt att lösa det på i databasen är att använda CASE-satsen. För månader blir det då en CASE-sats med tolv par av WHEN och THEN och för veckodagar blir det en CASE-sats med sju par av WHEN och THEN.

SELECT
    Name,
    BirthDate,
    Datename(WEEKDAY,BirthDate),
    CASE
        WHEN Datename(WEEKDAY,BirthDate) = 'Monday' THEN 'Måndag'
        WHEN Datename(WEEKDAY,BirthDate) = 'Tuesday' THEN 'Tisdag'
        WHEN Datename(WEEKDAY,BirthDate) = 'Wednesday' THEN 'Onsdag'
        WHEN Datename(WEEKDAY,BirthDate) = 'Thursday' THEN 'Torsdag'
        WHEN Datename(WEEKDAY,BirthDate) = 'Friday' THEN 'Fredag'
        WHEN Datename(WEEKDAY,BirthDate) = 'Saturday' THEN 'Lördag'
        WHEN Datename(WEEKDAY,BirthDate) = 'Sunday' THEN 'Söndag'
    END AS Dagar
FROM
    Personer;

Även om det fungerar känns det inte som en bra metod och ännu värre med månader som skulle kräva tolv par av WHEN och THEN. En bättre lösning kanske är att skapa ytterligare en tabell som fungerar som en liten ordlista, en kolumn med det svenska namnet för veckodagar och en kolumn för det engelska namnet?

SELECT
    Name,
    BirthDate,
    Swedish as Veckodagar
FROM
    Personer
    INNER JOIN SwedishWeekdays
    ON SwedishWeekdays.English = Datename(WEEKDAY,BirthDate);

Här kontrolleras om veckodagen som funktionen Datename returnerar matchar någon av de sju raderna i kolumnen ”English” i tabellen ”SwedishWeekdays” och när de matchar returneras värdet i kolumnen ”Swedish” på samma rad.

Jag vet inte om det finns någon anledning att använda sig av det senare exemplet heller, för det finns ett betydligt enklare sätt. Man ändrar helt enkelt standardspråket från engelska till svenska.

SET LANGUAGE swedish;

Här ändras standardspråket till svenska för den pågående sessionen. För att återgå till engelska inom samma session ändrar du ”swedish” till ”us_english” och kör koden en gång till. Det här var ju enkelt. Varför visade jag inte det här med en gång? Jo, för man lär sig mer och kommer framförallt ihåg saker och ting lättare genom att testa sig fram.

Datepart

Datepart ( datepart , date )

Returnerar en integer som representerar den specificerade delen av det angivna datumet. Värdet som returneras beror på språkmiljön som är satt genom att använda SET LANGUAGE och genom att konfigurera standardspråket för servern vid inloggning. Det senare tänker jag inte beskriva närmare just nu, men mer information finns på den här engelska sidan.

Första argumentet är den delen av datumet som en motsvarande integer ska returneras för t ex year, month, week, hour eller minute. Användardefinierade variabler är inte giltiga. Andra argumentet är ett uttryck som kan omvandlas till tid eller datum. Det kan vara ett uttryck, kolumnuttryck, användardefinierad variabel eller en sträng.

Datepart(weekday, Getdate()) AS 'Veckodag'

Här returneras siffran 3 om det är tisdag, eftersom söndag är den första dagen i veckan som standard.

För att ändra standardinställningen för första dagen i veckan till svenska, kan man använda SET LANGUAGE eller SET Datefirst.

Datediff

Datediff ( datepart , startdate , enddate )

Returnerar tiden mellan två datum. I exemplet nedan visas en ny kolumn med alias ”Period” och antalet år mellan BirthDate och 2016-03-17.

Obs. Resultatet blir inte exakt! Se DATEADD nedan.

SELECT
    Name,
    BirthDate,
    Datediff(YEAR, BirthDate, '2016-03-17') AS 'Period'
FROM
    Personer;

Datediff och Getdate

Man behöver inte bara använda en funktion per kalkyl, utan det går bra att kapsla in flera funktioner. I exemplet med Datediff ovan skrev jag in dagens datum 2016-03-17 (i skrivandets stund). Om det alltid är den aktuella dagens datum som jag vill använda, är det bättre att använda en funktion som visar aktuellt datum. Så i stället för ovanstående exempel, ska jag använda funktionen Getdate som en parameter till funktionen Datediff. Getdate visar aktuellt datum och aktuell tid och den tar inga argument.

SELECT
    Name,
    BirthDate,
    Datediff(YEAR, BirthDate, Getdate()) AS 'Period'
FROM
    Personer;

DATEADD

Dateadd (datepart , number , date )

Funktionen DATEADD används för att lägga till (eller dra ifrån) ett specificerat tidsintervall till ett datum. Första argumentet är den delen av datumet (t ex år eller månad) som man lägger till ett heltalsnummer. Andra argumentet är heltalet (positivt eller negativt) som läggs till. Om man anger ett värde med en decimal, avrundas det inte utan slängs bort. Det tredje argumentet är någon form av datum eller tid. Det kan vara ett uttryck, kolumnuttryck, användardefinierad variabel eller en sträng. Datatypen som returneras är samma typ som date-argumentet, förutom för strängar som i stället omvandlas till datetime.

Resultatet med Datediff ovan blir inte exakt, eftersom beräkningen görs för år. För att göra en exakt beräkning blir det mer komplicerat. Man börjar med att räkna ut födelsedagen för personen under nuvarande år. I det första argumentet specificerar man den delen av datumet som man ska lägga till ett heltal. Det andra argumentet är antalet påbörjade år och dessa år läggs på datumet i det tredje argumentet. Som andra argument har jag alltså lagt in exakt samma funktion som i föregående exempel med Datediff och Getdate, eftersom den returnerar antal påbörjade år.

Det tredje argumentet är det exakta datumet när personen föddes. Eftersom vi lägger till antalet påbörjade år (eftersom vi har angett år i första argumentet), blir resultatet nuvarande år, men månad och dag förblir det samma som datumet personen föddes. Nu har vi alltså födelsedagen för nuvarande år.

Dateadd(YEAR, Datediff(YEAR, BirthDate, Getdate()), BirthDate)

Nästa steg är att kontrollera om födelsedagen har passerat det här året eller inte. Det kan göras genom att lägga in det i en CASE-sats. I nedanstående kod kontrollerar jag först om nuvarande datum är mindre än födelsedatumet i år. Om det är det, subtraherar jag koden för antalet år med 1. Annars visas antalet år utan förändring.

SELECT
    Name,
    BirthDate,
    Datediff(YEAR, BirthDate, Getdate()) AS 'Period',
    Dateadd(YEAR, Datediff(YEAR, BirthDate, Getdate()), FilmReleaseDate),
    CASE
        WHEN Getdate() < Dateadd(YEAR, Datediff(YEAR, BirthDate, Getdate()), BirthDate)
        THEN Datediff(YEAR, BirthDate, Getdate())-1
        ELSE Datediff(YEAR, BirthDate, Getdate())
    END
FROM
    Personer;

@@Datefirst

Returnerar det nuvarande värdet för en session av SET Datefirst. SET Datefirst specificerar första dagen i veckan. Alltså 1 till 7, där måndag är 1. Standard för engelska (USA) är 7, eftersom söndagar räknas som första dagen i veckan där.

SET LANGUAGE us_english;
SELECT @@Datefirst;

Här returneras 7 och eftersom engelska (USA) redan är standard, hade jag egentligen inte behövt använda SET LANGUAGE. Om jag i stället hade valt ”Swedish” skulle resultatet blir 1.

Det går också att använda SET DATEFIRST för att ange vilken dag som är den första dagen i veckan.

SET Datefirst 1;

Här returneras 1 som betyder att måndag är den första dagen i veckan.

Year, Month och Day

Day

Day ( date )

Returnerar ett heltal som representerar dagen i månaden av det specificerade datumet.

Month

Month ( date )

Returnerar ett heltal som representerar månaden av det specificerade datumet.

Year

Year ( date )

Returnerar ett heltal som representerar året av det specificerade datumet.

De här funktionerna är användbara om man t ex vill visa personer som är födda ett visst år, en viss månad eller en viss dag.

SELECT
    BirthDate
FROM
    Personer
WHERE 
    Year(birthdate) = '1968' AND Month(birthdate) = '01' And Day(BirthDate) = '09';

Här visas enbart personer som är födda 1968-01-09.

Datum med < och >

Om du vill visa alla resultat före eller efter ett visst datum kan du använda operatorerna mindre än eller större än. För att nedanstående ska fungera måste datatypen för datumet vara av rätt typ som Date och String. Datetime fungerar inte eftersom den inkluderar även tid.

WHERE BirthDate &gt; '1950-03-22'

Här visas alla födelsedagar efter 1950-03-22.

Jämföra flera resultat med AND och OR

De med efternamn som börjar på ”Pa” och födelseår ”1947”.

WHERE
 LastName LIKE 'Pa%' AND Year(BirthDate) = '1947'

Datepart argument

Här nedan listas alla giltiga argument för datepart.

datepart Förkortningar
year yy, yyyy
quarter qq, q
month mm, m
dayofyear dy, y
day dd, d
week wk, ww
weekday dw, w
hour hh
minute mi, n
second ss, s
millisecond ms
microsecond mcs
nanosecond ns
TZoffset tz
ISO_WEEK ISOWK, ISOWW

Varje datepart och deras förkortningar returnerar samma värde.

Fler funktioner för datum och tid

CURRENT_TIMESTAMP, DATEDIFF_BIG (2016), DATEFROMPARTS, DATETIME2FROMPARTS, DATETIMEFROMPARTS, DATETIMEOFFSETFROMPARTS, EOMONTH, GETUTCDATE, ISDATE, SMALLDATETIMEFROMPARTS, SWITCHOFFSET, SYSDATETIME, SYSDATETIMEOFFSET, TIMEFROMPARTS, TODATETIMEOFFSET, DAY, MONTH, YEAR

Kommentera

E-postadressen publiceras inte. Obligatoriska fält är märkta *