Leniwce.com | blog technologiczny

MS SQL Konwersja dat na liczby i odwrotnie

Bartosz Lewandowski, 2009-04-22 09:40:30

Wielu programistów woli zapisywać datę w formacie liczbowym a nie formacie daty. Zapis taki jest o tyle wygodny, że nie trzeba się zastanawiać nad prawidłowym formatem daty, wynikającym z ustawień regionalnych serwera. Jednak zapis liczbowy wymusza konieczność konwersji pomiędzy datą a liczbą i odwrotnie.
W przypadku zapisu liczbowego należy określić jaką jednostkę czasu reprezentować będzie wartość liczbowa wprowadzona w naszym zapisie. Czyli, czy zwiększenie naszej liczby o jeden oznaczać będzie dzień, minutę, sekundę, rok, etc.

W MS SQL Server do konwersji dat możemy wykorzystać funkcję dateadd oraz datediff. Wyobraźmy sobie, że będziemy zapisywać nasz czas jako ilość sekund która upłynęła od pierwszego stycznia 2001 roku.
Najpierw dokonajmy konwersji daty na liczbę. Tu przyda się funkcja datediff (oblicza liczbę wybranych przedziałów czasowych pomiędzy dwoma datami), która przekształci nam datę na wartość liczbową.
Zgodnie z naszym założeniem, liczbą będzie ilość sekund, która upłynęła od daty 2001-01-01. W tym celu wykonajmy poniższe zapytanie (celowo deklaruję zmienną myTime, myślę, że tak będzie to czytelniejsze. Dodam, że może to być nazwa kolumny z tabeli lub wartość wpisana bezpośrednio do funkcji):

declare @myTime varchar(50)
set @myTime='2009-04-01'
select DATEDIFF([second], '2001-01-01', @myTime)

W funkcji tej pierwszy parametr określa jednostkę czasu (tu może być wartość taka jak: minute, hour, day, monht, year), drugi parametr to data początkowa, natomiast trzeci to data końcowa. Innymi słowy funkcja odejmuje od daty końcowej datę początkową, a wynik przedstawia jako ilość sekund. Parametry daty mogą zawierać również określenie czasu, gdy pomiajamy (jak w przykładzie) to system przyjmie domyślnie 00:00:00.
Gdy chcemy dodać opis czasu, podamy parametr myTime w postaci:

declare @myTime varchar(50)
set @myTime='2009-04-01 15:23:40'
select DATEDIFF([second], '2001-01-01', @myTime)

Jak wspomniałem, sami określamy w jakiej jednostce czasu ma być zwracana data. Gdy wystarczy nam dokładność do dnia to piszemy:

declare @myTime varchar(50)
set @myTime='2009-04-01'
select DATEDIFF([day], '2001-01-01', @myTime)

Świetnie, mamy więc datę zapisaną w formacie liczbowym, teraz więc zobaczmy jak ją ponownie przeliczyć na datę. I tu pomocna będzie funkcja dateadd, (dateadd zwraca datę powstałą przez dodanie do podanej daty określonego przedziału czasu).
Zobaczmy przykład:

declare @myTime int
set @myTime=260292220
select DATEADD([second], @myTime, '2001-01-01')

W przykładzie wynkiem jest właśnie 2009-04-01 15:23:40.
Parameter pierwszy określa jednostkę czasu (podobnie jak dla dateadd: minute, hour, day, monht, year). Parametr drugi to liczba dla której wyliczymy czas, parametr trzeci to znacznik czasu od którego rozpoczynamy liczenie.
Szalenie istotne jest jednak, aby podawane przez nas daty były interpretowane przez serwer w formacie dateTime. Inaczej mogą się pojawić poważne problemy z działaniem zapytań i

zamiast oczekiwanej przez nas daty pojawi się komunikat o błędzie, mówiący o problemie z konwersją dat. Przecież nie zawsze system jest w naszych, polskich ustawieniach. Może się okazać, ze podana przez nas data nie jest rozpoznawana przez serwer, że wartość ‘2001-01-01’ nie może być zinterpretowana jako data bo serwer ma w ustawieniach domyślną datę np. amerykańską (mm/dd/yy). W takiej sytuacji, należy podać format, jaki opisuje podany przez nas czas. Warto więc zastosować funkcję:

 select Convert(datetime, '1990-01-01',120)

Zadaniem podanego tu converta jest zapisanie daty (1990-01-01) w formacie dateTime, przy wykorzystaniu formatu „120”. Czyli zapewniamy, że podana przez nas data w formie tekstowej będzie parsowana jako data w formacie dateTime zgodnie z tym, czego oczekujemy. Dlaczego taki format? Dostępne formaty przedstawia poniższa tabelka:

Reasumując, prawidłowe funkcje konwertujące powinny wyglądać tak:

declare @myTime int
set @myTime=260292220
select DATEADD([second], @myTime, CONVERT(datetime, '2000-01-01',120))  
 
declare @myTimeDT dateTime -- wpisze do zmiennej czas po konwersji z string
set @myTimeDT = CONVERT(datetime, '2009-01-01',120)
select DATEDIFF([second], CONVERT(datetime, '1990-01-01',120),@myTimeDT)

Podsumowując, warto wspomnieć, że można utworzyć funkcję, które będą przeliczać odpowiednio czas na liczby. Wyglądać one mogą tak:
Czas na liczbę (parametr: dateTime):

create function dbo.myTimeToInt(@dt dateTime )
returns int
as
begin
  return DATEDIFF([second], CONVERT(datetime, '1990-01-01',120),@dt)
end

Liczba na czas (parametr: int):

create function dbo.intToMyTime(@ticks int)
returns dateTime
as
begin
  return DATEADD([second], @ticks, CONVERT(datetime, '2000-01-01',120))  
end

Wywołanie utworzonych funkcji (GETDATE() zwraca bieżacy czas):

select dbo.myTimeToInt(GETDATE())
select dbo.intToMyTime(98484344)

Powiązane artykuły

Backup wszystkich baz na serwerze (2009-05-01)

Komentarze

Czesieks [2009-11-09 10:25:50]

a jak zgodnie z powyższymi wskazanymi wygladać będzie zapytanie: od wybranej daty odjęcie miesiąca i dodania 1 dnia

kuba [2010-03-23 09:20:26]

Nie mogłem uporać się z błędem w projekcie i rozwiązaniem okazały się Twoje uwagi o potrzebie konwersji dat poprzez Convert(datetime,'data',120).
Dziękuję.

Arek [2010-06-14 15:02:10]

Ciekawy artykuł, tylko szkoda że tak późno go znalazłem, jak się już uporałem ze wszystkimi datami w moim projekcie :)

Kategorie

Android (2)

C# (13)

Inne (6)

Java (3)

JavaScript/JQuery (1)

Matlab (1)

MS Sql Server (2)

OpenGL (1)

PHP (2)

Najnowsze wpisy

Ostatnie komentarze