You may optionally enter a comparison operator (<, <=, >, >=, <> or =) at the beginning of each of your search values to specify how the comparison should be done.
-- http://www.sqlite.org/cvstrac/wiki?p=DateAndTimeFunctions
SELECT (strftime('%s','now') - strftime('%s',date('now','-1 day'))) AS Seconds
-- https://stackoverflow.com/questions/289680/difference-between-2-dates-in-sqlite
SELECT CAST(JulianDay('now') - JulianDay(MAX(datetime_field_in_table)) * 24 * 60 AS Integer) AS diff_minutes FROM example_table
209
SQLite
Datum, Zeit
Kalenderwoche eines Datums
-- Standard Weeknumber function from SQLite:
-- http://www.sqlite.org/lang_datefunc.html
SELECT strftime('%W', '2014-08-25')
-- The above calculation can lead to an wrong number if you expect an iso calendar week.
-- Therefore better:
-- http://stackoverflow.com/questions/15082584/sqlite-return-wrong-week-number-for-2013
SELECT
(strftime('%j', date('2014-08-25', '-3 days', 'weekday 4')) - 1) / 7 + 1 AS ISOCalendarWeekNumber;
211
SQLite
Datum, Zeit
Jahr aus Datum extrahieren
SELECT strftime('%Y', '2014-08-25') AS Year;
212
SQLite
Datum, Zeit
Tagesname
-- There is no build in solution. Because of this you have to build a solution out of the weekday.
-- Be careful, the first weekday (0) is a sunday
SELECT
,CASE CAST(strftime('%w', myDateField) as integer)
WHEN 0 then 'Sonntag'
WHEN 1 then 'Montag'
WHEN 2 then 'Dienstag'
WHEN 3 then 'Mittwoch'
WHEN 4 then 'Donnerstag'
WHEN 5 then 'Freitag'
WHEN 6 then 'Samstag'
ELSE '????????????????????' END AS Tag_der_Woche_Deutsch
,CASE CAST(strftime('%w', myDateField) as integer)
WHEN 0 then 'Sunday'
WHEN 1 then 'Monday'
WHEN 2 then 'Tuesday'
WHEN 3 then 'Wednesday'
WHEN 4 then 'Thursday'
WHEN 5 then 'Friday'
WHEN 6 then 'Saturday'
ELSE '????????????????????' END AS Day_of_week_English
223
SQLite
Strings
Strings miteinander verbinden
SELECT
field_a
,field_b
,field_a || field_b as together
FROM
table_1
134
SSIS
Fallunterscheidung
Wenn-Dann Abfrage
' Using IF(IIF) logic in Derived Column
(LEN(TRIM(Test)) > 0 ? SUBSTRING(Test,1,5) : NULL(DT_WSTR,5))
269
PL/pgSQL
Datenbank
UPDATE SQL mit JOIN
UPDATE TableToBeUpdated
SET myField1 = 'Update this field'
FROM
(
SELECT * FROM JoinedTable
WHERE cond1 = 1
)
J
WHERE J.ID = TableToBeUpdated.ID
;
275
PL/pgSQL
Datenbank
Stored Procedure anlegen/löschen
CREATE OR REPLACE FUNCTION increment(i INT) RETURNS void AS $$
BEGIN
INSERT INTO accounts (id,name, balance) VALUES (i, 'Test', 1200);
END; $$
LANGUAGE plpgsql;
DROP FUNCTION increment(i INT);
