Strings Mathematics Operators and Symbols General (Control Flow/Debugging) [TickCount] Dates Objects New Features Timelines Documentation Links Sections: Date Functions Time Zones Displaying local dates in 'yyyy-MM-dd' form (JavaScript) Month/Year Calculations: Add/Subtract/Difference Two Definitions For Weekdays AM/PM Leap Years Combining Date Parts Date Systems Current Year / Birth Year / Age Android / Kotlin / java.time 14 Year Types / Reusing Diaries / Week Numbers Excel: Calculating Date To Week Number Two Definitions For Remembrance Sunday Lower Priority Original Outline Note: UFL: Universal Function Library, a set of around 100-300 standard functions for all programming languages. Languages do not have to implement anything exactly to the UFL specification, it is a guide. See lower down for further details.
UFL: DateNowUTC [create a date (UTC), the date/time now] AutoHotkey: vDate := A_NowUTC [type: String] [e.g. '20060504030201'] [note: AutoHotkey uses 14-digit 'yyyyMMddHHmmss' strings/integers, substrings/'subints' of length 4/6/8/10/12 are also valid] C++: C#: Crystal: oDate = Time.utc [type: Time] Excel: ___ Excel VBA: ___ Go: Java: oDate = java.time.ZonedDateTime.now(java.time.ZoneOffset.UTC) [type: ZonedDateTime] [also: oDate = java.time.Instant.now()] [WARNING: java.time has ZonedDateTime/LocalDateTime/Instant/OffsetDateTime, LocalDateTime/Instant objects both lack a time zone, ZonedDateTime/LocalDateTime have a greater variety of methods than Instant e.g. format/getXXX/minusXXX/of/plusXXX/withXXX] JavaScript: oDate = new Date() [type: Date] Kotlin: oDate = java.time.ZonedDateTime.now(java.time.ZoneOffset.UTC) [type: ZonedDateTime] [also: oDate = java.time.Instant.now()] [WARNING: java.time has ZonedDateTime/LocalDateTime/Instant/OffsetDateTime, LocalDateTime/Instant objects both lack a time zone, ZonedDateTime/LocalDateTime have a greater variety of methods than Instant e.g. format/getXXX/minusXXX/of/plusXXX/withXXX] PHP: $oDate = date_create_immutable("now", timezone_open("UTC")) [type: DateTimeImmutable] Python: oDate = datetime.datetime.now().astimezone(datetime.timezone.utc) [type: datetime] R: Ruby: oDate = Time.now.utc [type: Time] [also: Time.new.utc] Rust: Scala: oDate = java.time.ZonedDateTime.now(java.time.ZoneOffset.UTC) [type: ZonedDateTime] [also: oDate = java.time.Instant.now()] [WARNING: java.time has ZonedDateTime/LocalDateTime/Instant/OffsetDateTime, LocalDateTime/Instant objects both lack a time zone, ZonedDateTime/LocalDateTime have a greater variety of methods than Instant e.g. format/getXXX/minusXXX/of/plusXXX/withXXX] SQL (MySQL): utc_timestamp() [note: utc_timestamp(3) for millisecond-precision] [related (date or time not both): utc_date()/utc_time()] [note: can omit parentheses for current_date/current_time/current_timestamp] SQL (PostgreSQL): now() AT TIME ZONE 'UTC' [note: no offset] [also (to get a '+00' offset, use a transaction, to temporarily set the time zone to UTC): BEGIN; SET LOCAL timezone = 'UTC'; SELECT 'now'::timestamptz; COMMIT;] [WARNING: trailing zeros in the microseconds are truncated, e.g. '2006-05-04 03:02:01.90000'::timestamp returns '2006-05-04 03:02:01.9'] [MAJOR WARNING: many/most date functions output dates as *local* time, ignoring any offsets in the input, so double-check that any date functions are doing what you expect, consider using SET timezone = 'UTC', for safety, either globally, or within a transaction] SQL (SQLite): datetime('now') [type: text] [e.g. '2006-05-04 03:02:01'] [also (millisecond accuracy): strftime('%Y-%m-%d %H:%M:%f', 'now')] [note ('%f'): '##.###'] Swift: UFL: DateNowLocal [create a date (local time zone), the date/time now][MAJOR WARNING: UFL functions ending 'local' may not be testable on online compilers that use UTC] AutoHotkey: vDate := A_Now [type: String] C++: C#: Crystal: oDate = Time.local [type: Time] Excel: =NOW() [note: days since 1899-12-30 (e.g. 2000-01-01 is 36526 days since 1899-12-30)] [type: 2 (Text)] [can use: TODAY() for the date only (with no time value)] Excel VBA: ___ [can use (second accuracy): oDate = Now] [type: Date] [also (approx. 10-millisecond accuracy): Do: oToday = Date: oDate = oToday + Timer / 86400: Loop Until oToday = Date] [WARNING: careful handling is used to combine Date and Timer, 2 date snapshots taken at different times (similar logic applies to Date + Time)] [note: Date/Time return a Date, Timer returns a Single] Go: Java: oDate = java.time.ZonedDateTime.now() [type: ZonedDateTime] [note: creates a date using the local time zone] JavaScript: ___ [can use: oDate = new Date()] [type: Date] [note: creates a UTC date that can be printed as local] Kotlin: oDate = java.time.ZonedDateTime.now() [type: ZonedDateTime] [note: creates a date using the local time zone] PHP: $oDate = date_create_immutable("now") [type: DateTimeImmutable] Python: oDate = datetime.datetime.now().astimezone() [type: datetime] [also (offset-naive): oDate = datetime.datetime.now()] [WARNING: using datetime.datetime.now() without astimezone() returns a local time, but with no associated time zone/offset, use astimezone() for an offset-aware date] R: Ruby: oDate = Time.now [type: Time] [also: Time.new] Rust: Scala: oDate = java.time.ZonedDateTime.now() [type: ZonedDateTime] [note: creates a date using the local time zone] SQL (MySQL): now() [note: now(3) for millisecond-precision] [also: current_timestamp()/localtime()/localtimestamp()/sysdate()] [related (date or time not both): curdate()/current_date()/current_time()/curtime()] [WARNING: localtime returns date and time, most 'time' functions omit date] [note: can omit parentheses for localtime/localtimestamp] SQL (PostgreSQL): now() [also (without offset): now()::timestamp] [WARNING: trailing zeros in the microseconds are truncated] [also (as keywords, without parentheses): current_date/current_time, current_timestamp, localtime, localtimestamp] [also: clock_timestamp(), timeofday(), statement_timestamp()/transaction_timestamp()] SQL (SQLite): datetime('now') [type: text] [note: creates a UTC date that can be printed as local] [also (millisecond accuracy): strftime('%Y-%m-%d %H:%M:%f', 'now')] Swift: UFL: DateNowSpecific [create a date in a specific time zone, the date/time now] AutoHotkey: ___ C++: C#: Crystal: oDate = Time.local Time::Location.load(vTZ) [also: p oDate = Time.local(location:Time::Location.load(vTZ))] [e.g. vTZ = "Europe/London"] Excel: ___ Excel VBA: ___ Go: Java: [FIXME] oDate = java.time.ZonedDateTime.now() [type: ZonedDateTime] JavaScript: ___ [can use: oDate = new Date()] [type: Date] [note: creates a UTC date that can be printed in a specific time zone] Kotlin: [FIXME] oDate = java.time.ZonedDateTime.now() [type: ZonedDateTime] [note: creates a date using the local time zone] PHP: $oDate = date_create_immutable("now", timezone_open($vTZ)) [e.g. $vTZ = "Europe/London"] Python: [FIXME] oDate = datetime.datetime.now().astimezone() [type: datetime] [also (offset-naive): oDate = datetime.datetime.now()] [WARNING: using datetime.datetime.now() without astimezone() returns a local time, but with no associated time zone/offset, use astimezone() for an offset-aware date] R: Ruby: [FIXME] oDate = Time.now [type: Time] [also: Time.new] Rust: Scala: [FIXME] oDate = java.time.ZonedDateTime.now() [type: ZonedDateTime] [note: creates a date using the local time zone] SQL (MySQL): [FIXME] now() [note: now(3) for millisecond-precision] [also: current_timestamp()/localtime()/localtimestamp()/sysdate()] [related (date or time not both): curdate()/current_date()/current_time()/curtime()] [WARNING: localtime returns date and time, most 'time' functions omit date] [note: can omit parentheses for localtime/localtimestamp] SQL (PostgreSQL): [FIXME] now() [also (without offset): now()::timestamp] [WARNING: trailing zeros in the microseconds are truncated] [also (as keywords, without parentheses): current_date/current_time, current_timestamp, localtime, localtimestamp] [also: clock_timestamp(), timeofday(), statement_timestamp()/transaction_timestamp()] SQL (SQLite): ___ Swift: UFL: DateFormatFriendly [or DateFormat][create a date string, show: weekday name, Y/M/D/H/M/S as integers, time zone or offset (if available)][see DateFormat for more details][see also: DateFormatISOUTC] AutoHotkey: vDate := FormatTime(vDate, "ddd yyyy-MM-dd HH:mm:ss") [note: no time zone to show] [note: internally, uses the Winapi's GetDateFormat/GetTimeFormat] C++: C#: Crystal: vDate = oDate.to_s("%a %Y-%m-%d %H:%M:%S %:z") [also (without weekday): oDate.to_s] [also: oDate.to_s("%c %:z")] [note: no asctime(), no ctime()] Excel: =TEXT(A1,"ddd yyyy-mm-dd hh:mm:ss.000") [note: no time zone to show] [WARNING: 'smart' behaviour is needed to determine whether 'm' means *month* or *minute* (e.g. by observing whether 'h'/'s' is near, 'm' by itself is month)] [WARNING: TEXT with 'ddd'/'dddd' gives incorrect values for Jan 1900 and Feb 1900 (e.g. 1900-01-01 was actually a Monday)] Excel VBA: vDate = Format(oDate, "ddd yyyy-mm-dd hh:mm:ss") [note: no time zone to show] [WARNING: 'smart' behaviour is needed to determine whether 'm' means *month* or *minute* (e.g. by observing whether 'h' precedes 'm', 'm' by itself is month)] [WARNING: Excel VBA also has 'n' for minutes, but Excel sheet functions lack this] [note: 'ddd'/'dddd' in Format and WorksheetFunction.Text give correct values for Jan 1900 and Feb 1900] Go: Java: vDate = oDate.format(java.time.format.DateTimeFormatter.ofPattern("E yyyy-MM-dd HH:mm:ss Z VV")) JavaScript: oDate.toString() [also: oDate.toUTCString()] [also: oDate.toLocaleString()] [also: oDate.toLocaleString("en-GB", {timeZone:vTZ, dateStyle:"full", timeStyle:"full"})] [e.g. vTZ = "UTC", vTZ = "Europe/London"] [note: toLocaleString() has various options e.g. oDate.toLocaleString("en-CA-u-hc-h23", {weekday:"short", year:"numeric", month:"2-digit", day:"2-digit", hour:"2-digit", minute:"2-digit", second:"2-digit", timeZoneName:"longOffset"})] [also: Intl.RelativeTimeFormat, oDate.toISOString(), oDate.toDateString()/oDate.toTimeString(), oDate.toLocaleDateString()/oDate.toLocaleTimeString()] Kotlin: vDate = oDate.format(java.time.format.DateTimeFormatter.ofPattern("E yyyy-MM-dd HH:mm:ss Z VV")) PHP: $vDate = $oDate->format("D Y-m-d H:i:s P") [also: $vDate = date_format($oDate, "D Y-m-d H:i:s P")] Python: vDate = oDate.strftime("%a %Y-%m-%d %H:%M:%S %:z") [WARNING: before Python 3.12, '%:z' printed the literal string '%:z'] [also (without weekday): str(oDate)] [also: oDate.strftime("%c %:z")] [also (without time zone, equivalent to '%c'): oDate.ctime()] [e.g. '%:z': '+01:00', e.g. '%z': '+0100'] R: Ruby: vDate = oDate.strftime("%a %Y-%m-%d %H:%M:%S %:z") [also (without weekday): oDate.to_s] [also: oDate.strftime("%c %:z")] [also (without time zone, equivalent to '%c'): oDate.asctime()] [alias (asctime): ctime] Rust: Scala: vDate = oDate.format(java.time.format.DateTimeFormatter.ofPattern("E yyyy-MM-dd HH:mm:ss Z VV")) SQL (MySQL): date_format(MyDate, '%a %Y-%m-%d %H:%i:%S.%f') [note: no time zone to show] [WARNING: '%M' is month name, use '%i' for minutes] [also: date_format(now(), get_format(DATETIME, 'ISO'))] [related: time_format()] [WARNING: '%f' is 6-digit microseconds (i.e. 3-digit milliseconds and 3-digit microseconds)] SQL (PostgreSQL): to_char(now(), 'Dy YYYY-MM-DD HH24:MI:SS.MS OF') SQL (SQLite): '["Sun","Mon","Tue","Wed","Thu","Fri","Sat"]' ->> strftime('%w', MyDate) || strftime(' %Y-%m-%d %H:%M:%S', MyDate) [note: doesn't show time zone] [also (print UTC as local): datetime(MyDate, 'localtime')] [also (print UTC as local with milliseconds): strftime('%Y-%m-%d %H:%M:%f', MyDate, 'localtime')] [WARNING: '%f' is 'SS.SSS', i.e. seconds and milliseconds] Swift: UFL: DateSetTZLocationLocalDemo [set the local time zone][e.g. 'Europe/London', 'America/New_York', 'UTC', 'GMT', 'Etc/GMT+12', 'Etc/GMT-14'] AutoHotkey: ___ C++: ___ C#: ___ [can use (print date as local): var oTZ = TimeZoneInfo.FindSystemTimeZoneById("Europe/London"); var vDate = TimeZoneInfo.ConvertTime(oDate, oTZ).ToString("yyyy-MM-dd HH:mm:ss");] [e.g. var oDate = DateTime.Now] Crystal: Time::Location.local = Time::Location.load("Europe/London") Excel: ___ Excel VBA: ___ Go: os.Setenv("TZ", "Europe/London") Java: java.util.TimeZone.setDefault(java.util.TimeZone.getTimeZone("Europe/London")) JavaScript: ___ [can use (Node.js): process.env.TZ = "Europe/London"] Kotlin: java.util.TimeZone.setDefault(java.util.TimeZone.getTimeZone("Europe/London")) PHP: date_default_timezone_set("Europe/London") Python: import os, time; os.environ["TZ"] = "Europe/London"; time.tzset() [requires: import os] [requires: import time] R: Sys.setenv(TZ="Europe/London") Ruby: ENV["TZ"] = "Europe/London" Rust: ___ [can use (print date as local): let mut oDate = Utc::now().with_timezone(&FixedOffset::east_opt(1*3600).unwrap()); let mut vDate = oDate.to_string();] [also: Local::now()] [requires: use chrono::prelude::*] Scala: java.util.TimeZone.setDefault(java.util.TimeZone.getTimeZone("Europe/London")) SQL (MySQL): SET time_zone = 'Europe/London' [also: SET time_zone = '+01:00'] [e.g. temporarily set time zone to UTC: SET @vTimeZoneLast = @@time_zone; SET time_zone = 'UTC'; SELECT now(); SET time_zone = @vTimeZoneLast;] SQL (PostgreSQL): SET timezone = 'Europe/London' [WARNING: inverted sign, for '+01:00', use: SET timezone = '-01:00'] [e.g. temporarily set time zone to UTC: BEGIN; SET LOCAL timezone = 'UTC'; SELECT 'now'::timestamptz; COMMIT;] SQL (SQLite): ___ Swift: ___ [can use (print date as local): var oFormat = DateFormatter(); oFormat.dateFormat = "yyyy-MM-dd HH:mm:ss"; oFormat.timeZone = TimeZone(identifier:"Europe/London"); var vDate = oFormat.string(from:oDate)] [e.g. var oDate = Date()] [requires: import Foundation] [also: oFormat.timeZone = TimeZone(abbreviation:"BST")] UFL: DateInternal [details of how dates are represented internally e.g. min/max/precision] AutoHotkey: second precision, 14-digit int/string, of the form 'yyyyMMddHHmmss' (or a substring), min: '1601-01-01', max: '9999-12-31 23:59:59' C++: C#: Crystal: 'Internally, the time is stored as an Int64 representing seconds from epoch (0001-01-01 00:00:00.0 UTC) and an Int32 representing nanosecond-of-second with value range 0..999_999_999.' 'The supported date range is 0001-01-01 00:00:00.0 to 9999-12-31 23:59:59.999_999_999 in any local time zone.' Excel: millisecond precision, days since 1899-12-30, min: '1900-01-01' (as 1 in Excel sheets, as 2 in Excel VBA), max (note '499'): '9999-12-31 23:59:59.499'] [WARNING: 1900-01-01 to 1900-02-28 have internal values 1 to 59 inclusive, due to the inclusion of the non-existent day 1900-02-29 (for Lotus 1-2-3 compatibility)] [note (Excel sheets and VBA): '1900-03-01' as 61, '9999-12-31' as 2958465] Excel VBA: millisecond precision, days since 1899-12-30, min: '0100-01-01' (as -657434), max: '9999-12-31 23:59:59.999' [note: 1900-01-01 to 1900-02-28 have the correct internal values 2 to 60 inclusive] [note: 1900-03-01 has value 61 in Excel sheets and Excel VBA] [note (Excel sheets and VBA): '1900-03-01' as 61, '9999-12-31' as 2958465] [e.g. object to float: vDate = CDbl(oDate)] [also: object to float: vDate = oDate * 1] [e.g. float to object: oDate = CDate(vDate)] Go: Java: nanosecond precision, min: '-999999999-01-01T00:00:00', max: '+999999999-12-31T23:59:59.999999999' JavaScript: milliseconds since 1970-01-01, 'range from April 20, 271821 BC to September 13, 275760 AD' [e.g. oDate.valueOf()] Kotlin: nanosecond precision, min: '-999999999-01-01T00:00:00', max: '+999999999-12-31T23:59:59.999999999' PHP: DateTimeImmutable objects have a 'date' property (microsecond precision), 'timezone_type'/'timezone' properties [note: DateTimeImmutable: 'This class behaves the same as DateTime except new objects are returned when modification methods such as DateTime::modify() are called.'] Python: microsecond precision, min: '0001-01-01', max: '9999-12-31 23:59:59.999999' R: Ruby: 'The value represents the number of nanoseconds from Epoch. The signed 63 bit integer can represent 1823-11-12 to 2116-02-20. When Integer or Rational object is used (before 1823, after 2116, under nanosecond), Time works slower than when the signed 63 bit integer is used.' Rust: Scala: nanosecond precision, min: '-999999999-01-01T00:00:00', max: '+999999999-12-31T23:59:59.999999999' SQL (MySQL): 'the range for DATETIME values is '1000-01-01 00:00:00.000000' to '9999-12-31 23:59:59.499999', and the range for TIMESTAMP values is '1970-01-01 00:00:01.000000' to '2038-01-19 03:14:07.499999'.' [also: 'TO_DAYS() is not intended for use with values that precede the advent of the Gregorian calendar (1582), because it does not take into account the days that were lost when the calendar was changed.'] SQL (PostgreSQL): timestamp with/without time zone, 8 bytes, 4713 BC to 294276 AD, resolution: 1 microsecond SQL (SQLite): millisecond precision, min: '-4713-11-24 12:00:00', max: '9999-12-31 23:59:59.999' Swift: UFL: DateTodayUTC [create a date (UTC), the date/time now, with h/m/s set to 0] AutoHotkey: vDate := FormatTime(A_NowUTC, "yyyyMMdd000000") C++: C#: Crystal: oDate = Time.utc.at_beginning_of_day Excel: ___ Excel VBA: ___ Go: Java: oDate = java.time.ZonedDateTime.now(java.time.ZoneOffset.UTC).truncatedTo(java.time.temporal.ChronoUnit.DAYS) JavaScript: oDate = new Date(); oDate.setUTCHours(0, 0, 0, 0); Kotlin: oDate = java.time.ZonedDateTime.now(java.time.ZoneOffset.UTC).truncatedTo(java.time.temporal.ChronoUnit.DAYS) PHP: $oDate = date_create_immutable("today", timezone_open("UTC")) Python: oDate = datetime.datetime.combine(datetime.date.today(), datetime.datetime.min.time(), tzinfo=datetime.timezone.utc)] R: Ruby: ___ [can use: oDate = Time.now.utc; oDate = Time.utc(oDate.year, oDate.month, oDate.mday)] Rust: Scala: oDate = java.time.ZonedDateTime.now(java.time.ZoneOffset.UTC).truncatedTo(java.time.temporal.ChronoUnit.DAYS) SQL (MySQL): timestamp(utc_date()) SQL (PostgreSQL): (now() AT TIME ZONE 'UTC')::date::timestamp [note: no offset] SQL (SQLite): date('now') [also: datetime(date('now'))] [also: strftime('%Y-%m-%d 00:00:00', 'now')] Swift: UFL: DateTodayLocal [create a date (local time zone), the date/time now, with h/m/s set to 0][WARNING: check that midnight is being returned, and not some other hour] AutoHotkey: vDate := FormatTime(A_Now, "yyyyMMdd000000") [also: vDate := FormatTime("", "yyyyMMdd000000")] C++: C#: Crystal: oDate = Time.local.at_beginning_of_day Excel: =TODAY() [note: days since 1899-12-30] Excel VBA: oDate = Date Go: Java: oDate = java.time.ZonedDateTime.now().truncatedTo(java.time.temporal.ChronoUnit.DAYS) [also (date with no time zone): oDate = java.time.LocalDateTime.of(java.time.LocalDate.now(), java.time.LocalTime.of(0, 0))] JavaScript: oDate = new Date(); oDate.setHours(0, 0, 0, 0); Kotlin: oDate = java.time.ZonedDateTime.now().truncatedTo(java.time.temporal.ChronoUnit.DAYS) [also (date with no time zone): oDate = java.time.LocalDateTime.of(java.time.LocalDate.now(), java.time.LocalTime.of(0, 0))] PHP: $oDate = date_create_immutable("today") Python: oDate = datetime.datetime.combine(datetime.date.today(), datetime.datetime.min.time()) [also: to always print the time zone (offset-aware rather than offset-naive): oDate = datetime.datetime.combine(datetime.date.today(), datetime.datetime.min.time()).astimezone()] [also (date object, not datetime object): oDate = datetime.date.today()] R: Ruby: ___ [can use: oDate = Time.now; oDate = Time.new(oDate.year, oDate.month, oDate.mday)] Rust: Scala: oDate = java.time.ZonedDateTime.now().truncatedTo(java.time.temporal.ChronoUnit.DAYS) [also (date with no time zone): oDate = java.time.LocalDateTime.of(java.time.LocalDate.now(), java.time.LocalTime.of(0, 0))] SQL (MySQL): timestamp(curdate()) [alias: current_date(), current_date] SQL (PostgreSQL): 'today'::timestamptz [also: 'today'::timestamp with time zone] [also: to_char(now(), 'YYYY-MM-DD 00:00:00OF')] [also (without offset): now()::date::timestamp] SQL (SQLite): date('now', 'localtime') [also: datetime(date('now'), 'localtime')] [also: strftime('%Y-%m-%d 00:00:00', 'now', 'localtime')] Swift: UFL: DateTodaySpecific [create a date in a specific time zone, the date/time now, with h/m/s set to 0][WARNING: check that midnight is being returned, and not some other hour] AutoHotkey: ___ C++: C#: Crystal: oDate = (Time.local Time::Location.load(vTZ)).at_beginning_of_day [also: oDate = Time.local(location:Time::Location.load(vTZ)).at_beginning_of_day] [e.g. vTZ = "Europe/London"] Excel: ___ Excel VBA: ___ Go: Java: [FIXME] oDate = java.time.ZonedDateTime.now().truncatedTo(java.time.temporal.ChronoUnit.DAYS) [also (date with no time zone): oDate = java.time.LocalDateTime.of(java.time.LocalDate.now(), java.time.LocalTime.of(0, 0))] JavaScript: [FIXME] oDate = new Date(); oDate.setHours(0, 0, 0, 0); Kotlin: [FIXME] oDate = java.time.ZonedDateTime.now().truncatedTo(java.time.temporal.ChronoUnit.DAYS) [also (date with no time zone): oDate = java.time.LocalDateTime.of(java.time.LocalDate.now(), java.time.LocalTime.of(0, 0))] PHP: $oDate = date_create_immutable("today", timezone_open($vTZ)) [e.g. $vTZ = "Europe/London"] Python: [FIXME] oDate = datetime.datetime.combine(datetime.date.today(), datetime.datetime.min.time()) [also: to always print the time zone (offset-aware rather than offset-naive): oDate = datetime.datetime.combine(datetime.date.today(), datetime.datetime.min.time()).astimezone()] [also (date object, not datetime object): oDate = datetime.date.today()] R: Ruby: [FIXME] ___ [can use: oDate = Time.now; oDate = Time.new(oDate.year, oDate.month, oDate.mday)] Rust: Scala: [FIXME] oDate = java.time.ZonedDateTime.now().truncatedTo(java.time.temporal.ChronoUnit.DAYS) [also (date with no time zone): oDate = java.time.LocalDateTime.of(java.time.LocalDate.now(), java.time.LocalTime.of(0, 0))] SQL (MySQL): [FIXME] timestamp(curdate()) [alias: current_date(), current_date] SQL (PostgreSQL): [FIXME] 'today'::timestamptz [also: 'today'::timestamp with time zone] [also: to_char(now(), 'YYYY-MM-DD 00:00:00OF')] [also (without offset): now()::date::timestamp] SQL (SQLite): [FIXME] date('now', 'localtime') [also: datetime(date('now'), 'localtime')] [also: strftime('%Y-%m-%d 00:00:00', 'now', 'localtime')] Swift: UFL: DateNowMilliseconds [or DateNowMSec/DateNowMSecLocal/DateNowMSecUTC][get the current milliseconds (0-999) (local time zone)][note: local/UTC milliseconds will be identical] AutoHotkey: vMSec := A_MSec C++: C#: Crystal: vMSec = Time.utc.millisecond [also: vMSec = Time.local.millisecond] Excel: =RIGHT(TEXT(NOW(),"s.000"),3) Excel VBA: vMSec = (Timer * 1000) Mod 1000 Go: Java: vMSec = java.time.ZonedDateTime.now().getNano() / 1000000 [note: truncated division] JavaScript: vMSec = new Date().getMilliseconds() Kotlin: vMSec = java.time.ZonedDateTime.now().getNano() / 1000000 [note: truncated division] PHP: $vMSec = date_create_immutable("now")->format("v") Python: vMSec = datetime.datetime.now().microsecond // 1000 R: Ruby: vMSec = Time.new.usec / 1000 [note: floor division] Rust: Scala: vMSec = java.time.ZonedDateTime.now().getNano() / 1000000 [note: truncated division] SQL (MySQL): left(date_format(now(3), '%f'), 3) [note ('%f', milliseconds/microseconds): '######'] SQL (PostgreSQL): to_char(now(), 'MS') [also: extract(milliseconds from now())::int % 1000] SQL (SQLite): substr(strftime('%f', 'now'), 4) [note ('%f', seconds/milliseconds): '##.###'] Swift: UFL: DateMakeUTCDemo [create a date (UTC)][show examples of dates with/without time portion (with milliseconds if supported)][see also: DateAddMilliseconds] AutoHotkey: vDate := 20060504030201 [e.g. vDate := 20060504000000] [note: milliseconds not supported] [WARNING: the user must store location/time zone separately] [e.g. vDate := Format("{:04}{:02}{:02}{:02}{:02}{:02}", 2006, 5, 4, 3, 2, 1)] C++: C#: Crystal: oDate = Time.utc(2006, 5, 4, 3, 2, 1, nanosecond:999000000) [e.g. oDate = Time.utc(2006, 5, 4)] Excel: =DATE(2006,5,4)+TIME(3,2,1)+999/(86400*1000) [WARNING: the user must store location/time zone separately] Excel VBA: oDate = DateSerial(2006, 5, 4) + TimeSerial(3, 2, 1) + 999 / (86400 * 1000) [WARNING: the user must store location/time zone separately] Go: Java: oDate = java.time.ZonedDateTime.of(2006, 5, 4, 3, 2, 1, 999000000, java.time.ZoneOffset.UTC) [e.g. oDate = java.time.ZonedDateTime.of(2006, 5, 4, 0, 0, 0, 0, java.time.ZoneOffset.UTC)] JavaScript: oDate = new Date(Date.UTC(2006, 5-1, 4, 3, 2, 1, 999)) [e.g. oDate = new Date(Date.UTC(2006, 5-1, 4))] [WARNING: month is 0-based] [WARNING: 1 param: 'new Date(vMSecSince1970)' versus 'new Date(Date.UTC(vYear))'] Kotlin: oDate = java.time.ZonedDateTime.of(2006, 5, 4, 3, 2, 1, 999000000, java.time.ZoneOffset.UTC) [e.g. oDate = java.time.ZonedDateTime.of(2006, 5, 4, 0, 0, 0, 0, java.time.ZoneOffset.UTC)] PHP: $oDate = date_create_immutable(sprintf("%d-%d-%d %d:%d:%d.%03d", 2006, 5, 4, 3, 2, 1, 999), timezone_open("UTC")) [e.g. $oDate = date_create_immutable(sprintf("%d-%d-%d", 2006, 5, 4), timezone_open("UTC"))] [WARNING: mktime() has an unusual parameter order: hour, min, sec, *month*, *day*, year] Python: oDate = datetime.datetime(2006, 5, 4, 3, 2, 1, 999000, tzinfo=datetime.timezone.utc) [e.g. oDate = datetime.datetime(2006, 5, 4, tzinfo=datetime.timezone.utc)] [note: throws if given invalid input] R: Ruby: oDate = Time.utc(2006, 5, 4, 3, 2, 1, 999000) [e.g. oDate = Time.utc(2006, 5, 4)] [WARNING: has a microseconds parameter, unlike Time.new()] Rust: Scala: oDate = java.time.ZonedDateTime.of(2006, 5, 4, 3, 2, 1, 999000000, java.time.ZoneOffset.UTC) [e.g. oDate = java.time.ZonedDateTime.of(2006, 5, 4, 0, 0, 0, 0, java.time.ZoneOffset.UTC)] SQL (MySQL): ___ [can use: timestamp(concat_ws('-', 2006, 5, 4), concat_ws(':', 3, 2, '1.999'))] [related: makedate(), maketime()] [WARNING: makedate() takes a year and day-of-year] SQL (PostgreSQL): make_timestamp(2006, 5, 4, 3, 2, 1) [note: no offset] [also: make_date()/make_time(), make_interval(), make_timestamptz()] SQL (SQLite): format('%04d-%02d-%02d %02d:%02d:%02d.%03d', 2006, 5, 4, 3, 2, 1, 999) [also: format('%04d-%02d-%02d 00:00:00', 2006, 5, 4)] [also: datetime(format('%04d-%02d-%02d', 2006, 5, 4))] Swift: UFL: DateMakeLocalDemo [create a date (local time zone)][show examples of dates with/without time portion (with milliseconds if supported)][see also: DateAddMilliseconds] AutoHotkey: vDate := 20060504030201 [e.g. vDate := 20060504000000] [note: milliseconds not supported] [WARNING: the user must store location/time zone separately] [e.g. vDate := Format("{:04}{:02}{:02}{:02}{:02}{:02}", 2006, 5, 4, 3, 2, 1)] C++: C#: Crystal: oDate = Time.local(2006, 5, 4, 3, 2, 1, nanosecond:999000000) [e.g. oDate = Time.local(2006, 5, 4)] Excel: =DATE(2006,5,4)+TIME(3,2,1) [WARNING: the user must store location/time zone separately] Excel VBA: oDate = DateSerial(2006, 5, 4) + TimeSerial(3, 2, 1) [WARNING: the user must store location/time zone separately] Go: Java: oDate = java.time.ZonedDateTime.of(2006, 5, 4, 3, 2, 1, 999000000, java.time.ZoneId.systemDefault()) [e.g. oDate = java.time.ZonedDateTime.of(2006, 5, 4, 0, 0, 0, 0, java.time.ZoneId.systemDefault())] JavaScript: oDate = new Date(2006, 5-1, 4, 3, 2, 1, 999) [e.g. oDate = new Date(2006, 5-1, 4)] [WARNING: month is 0-based] [note: date objects are UTC, but dates can be printed as UTC/local/custom via toLocaleString()] [WARNING: 1 param: 'new Date(vMSecSince1970)' versus 'new Date(Date.UTC(vYear))'] Kotlin: oDate = java.time.ZonedDateTime.of(2006, 5, 4, 3, 2, 1, 999000000, java.time.ZoneId.systemDefault()) [e.g. oDate = java.time.ZonedDateTime.of(2006, 5, 4, 0, 0, 0, 0, java.time.ZoneId.systemDefault())] PHP: $oDate = date_create_immutable(sprintf("%d-%d-%d %d:%d:%d.%03d", 2006, 5, 4, 3, 2, 1, 999)) [e.g. $oDate = date_create_immutable(sprintf("%d-%d-%d", 2006, 5, 4))] Python: oDate = datetime.datetime(2006, 5, 4, 3, 2, 1, 999000) [e.g. oDate = datetime.datetime(2006, 5, 4)] [also: to always print the time zone: oDate = datetime.datetime(2006, 5, 4, 3, 2, 1, 999000).astimezone()] [note: throws if given invalid input] R: Ruby: oDate = Time.new(2006, 5, 4, 3, 2, 1.999, precision:3) [e.g. oDate = Time.new(2006, 5, 4)] Rust: Scala: oDate = java.time.ZonedDateTime.of(2006, 5, 4, 3, 2, 1, 999000000, java.time.ZoneId.systemDefault()) [e.g. oDate = java.time.ZonedDateTime.of(2006, 5, 4, 0, 0, 0, 0, java.time.ZoneId.systemDefault())] SQL (MySQL): ___ [can use (create local date, store it as UTC): e.g. convert_tz(timestamp(concat_ws('-', 2006, 5, 4), concat_ws(':', 3, 2, '1.999')), 'Europe/London', '+00:00')] [MAJOR WARNING: timestamp('2006-05-04T03:02:01+01:00'), it will create a UTC date and apply the offset, then convert to the *local* time zone, then store it as UTC, workaround: temporarily set the time zone to UTC] SQL (PostgreSQL): make_timestamptz(2006, 5, 4, 3, 2, 1) SQL (SQLite): ___ [can use (create local date, store it as UTC): e.g. datetime(format('%04d-%02d-%02d %02d:%02d:%02d.%03d', 2006, 5, 4, 3, 2, 1, 999), 'utc', 'subsec')] [also: datetime(format('%04d-%02d-%02d', 2006, 5, 4), 'utc')] [note: the 'utc' modifier 'assumes that the time-value to its left is in the local timezone and adjusts that time-value to be in UTC'] Swift: UFL: DateMakeLocalWithFoldDemo [create a date (local time zone)][local time zones often have an ambiguous hour when DST ends, e.g. 1 AM occurs twice, a fold attribute specifies which][e.g. 2000-10-29, 4-hour period (BST/GMT): 12 AM ('0 AM') +0100, 1 AM +0100, 1 AM +0000, 2AM +0100] AutoHotkey: ___ C++: C#: Crystal: ___ Excel: ___ Excel VBA: ___ Go: Java: oDate = java.time.ZonedDateTime.parse("1999-10-31T01:30:00+00:00[Europe/London]") [e.g. the UK: use '+01:00'/'+00:00', for the earlier/later time] JavaScript: oDate = new Date(Date.parse("1999-10-31 01:30:00 +00:00")) [e.g. the UK: use '+01:00'/'+00:00' or 'GMT+0100'/'GMT+0000', for the earlier/later time] Kotlin: oDate = java.time.ZonedDateTime.parse("1999-10-31T01:30:00+00:00[Europe/London]") [e.g. the UK: use '+01:00'/'+00:00', for the earlier/later time] PHP: ___ Python: oDate = datetime.datetime(1999, 10, 31, 1, 30, 0, fold=0) [note: set fold to 0 or 1, for the earlier/later time] [e.g. the UK: 1999-10-31, had 1am BST followed by 1am GMT] R: Ruby: ___ Rust: Scala: oDate = java.time.ZonedDateTime.parse("1999-10-31T01:30:00+00:00[Europe/London]") [e.g. the UK: use '+01:00'/'+00:00', for the earlier/later time] SQL (MySQL): ___ SQL (PostgreSQL): ___ SQL (SQLite): ___ Swift: UFL: DateMakeSpecificDemo [create a date (specific time zone)][show examples of dates with/without time portion (with milliseconds if supported)][workaround: create a UTC date, add the time zone offset][see also: DateAddMilliseconds] AutoHotkey: ___ C++: C#: Crystal: oDate = Time.local(2006, 5, 4, 3, 2, 1, nanosecond:999000000, location:Time::Location.load(vTZ)) [e.g. vTZ = "Europe/London"] Excel: ___ Excel VBA: ___ Go: Java: oDate = java.time.ZonedDateTime.of(2006, 5, 4, 3, 2, 1, 999000000, oTZ) [e.g. oDate = java.time.ZonedDateTime.of(2006, 5, 4, 0, 0, 0, 0, oTZ)] [e.g. oTZ = java.time.ZoneId.of("Europe/London")] JavaScript: ___ [can use (create UTC date, then add offset): e.g. oDate = new Date(Date.UTC(2006, 5-1, 4, 3, 2, 1, 999)); vOffsetMSec = (Date.parse(oDate.toLocaleString("en",{timeZone:vTZ})+" Z")-Date.parse(oDate.toLocaleString("en",{timeZone:"UTC"})+" Z")); oDate = new Date(oDate.valueOf() + vOffsetMSec);] [WARNING: month is 0-based] [note: date objects are UTC, but dates can be printed as UTC/local/custom via toLocaleString()] Kotlin: oDate = java.time.ZonedDateTime.of(2006, 5, 4, 3, 2, 1, 999000000, oTZ) [e.g. oDate = java.time.ZonedDateTime.of(2006, 5, 4, 0, 0, 0, 0, oTZ)] [e.g. oTZ = java.time.ZoneId.of("Europe/London")] PHP: $oDate = date_create_immutable(sprintf("%d-%d-%d %d:%d:%d.%03d", 2006, 5, 4, 3, 2, 1, 999), timezone_open($vTZ)) [e.g. $oDate = date_create_immutable(sprintf("%d-%d-%d", 2006, 5, 4), timezone_open($vTZ))] [e.g. $vTZ = "Europe/London"] Python: oDate = datetime.datetime(2006, 5, 4, 3, 2, 1, 999000, tzinfo=oTZ) [e.g. oDate = datetime.datetime(2006, 5, 4, tzinfo=oTZ)] [e.g. oTZ = zoneinfo.ZoneInfo("Europe/London")] [note: throws if given invalid input] R: Ruby: ___ [can use: vTZDefault = ENV["TZ"]; ENV["TZ"] = vTZ; oDate = Time.new(2006, 5, 4, 3, 2, 1.999, precision:3); ENV["TZ"] = vTZDefault] [e.g. vTZ = "Europe/London"] Rust: Scala: oDate = java.time.ZonedDateTime.of(2006, 5, 4, 3, 2, 1, 999000000, oTZ) [e.g. oDate = java.time.ZonedDateTime.of(2006, 5, 4, 0, 0, 0, 0, oTZ)] [e.g. oTZ = java.time.ZoneId.of("Europe/London")] SQL (MySQL): ___ [can use (create date with offset, store it as UTC): e.g. convert_tz(timestamp(concat_ws('-', 2006, 5, 4), concat_ws(':', 3, 2, '1.999')), 'Europe/London', '+00:00')] SQL (PostgreSQL): ___ [e.g. make_timestamptz(2006, 5, 4, 3, 2, 1, 'Europe/London')] [WARNING: will create a timestamp with an offset, with the offset based on the *local* time zone] SQL (SQLite): ___ [can use (create date with offset, store it as UTC): e.g. datetime(format('%04d-%02d-%02d %02d:%02d:%02d.%03d +01:00', 2006, 5, 4, 3, 2, 1, 999), 'subsec')] [also: datetime(format('%04d-%02d-%02d 00:00 +01:00', 2006, 5, 4))] Swift: UFL: DateMakeValidRange [when creating dates from parts, is '31 Apr' valid, is '0 Apr' valid etc][note: consider months/days, ignore other time units][note: when invalid dates are accepted, wraparound is typically applied][e.g. test the invalid date '2001-02-29', which if accepted, typically becomes '2001-03-01'][see also: DateUTCDemo/DateLocalDemo/DateSpecificDemo/DateSetYear/DateSetMonth/DateSetDay] AutoHotkey: there is no make date function (a date is a 14-or-fewer-digit string/int, so invalid dates could be constructed, but IsTime/FormatTime/DateAdd/DateDiff only accept valid dates) C++: C#: Crystal: Time.utc()/Time.local(): year/month/day must be a valid date Excel: DATE(): month/day can take any int, wraparound is applied Excel VBA: DateSerial(): month/day can take any int, wraparound is applied Go: Java: java.time.ZonedDateTime.of(): year/month/day must be a valid date JavaScript: new Date(): month/day can take any int, wraparound is applied [WARNING: months are 0-based, e.g. Jan is 1-1=0, e.g. Dec is 12-1=11] Kotlin: java.time.ZonedDateTime.of(): year/month/day must be a valid date PHP: date_create_immutable(): month must be in 0..12, day must be in 0..31, invalid dates that meet those criteria are accepted Python: datetime.datetime(): year/month/day must be a valid date R: Ruby: Time.utc()/Time.new(): month must be in 1..12, day must be in 1..31, invalid dates that meet those criteria are accepted (i.e. '29 Feb' for common years, '30 Feb', '31 Feb/Apr/Jun/Sep/Nov') Rust: Scala: java.time.ZonedDateTime.of(): year/month/day must be a valid date SQL (MySQL): timestamp(): year/month/day must be a valid date SQL (PostgreSQL): make_timestamp(): year/month/day must be a valid date SQL (SQLite): datetime(): month must be in 1..12, day must be in 1..31, invalid dates that meet those criteria are accepted (i.e. '29 Feb' for common years, '30 Feb', '31 Feb/Apr/Jun/Sep/Nov') [e.g. datetime('2001-02-29', '0 days') returns '2001-03-01 00:00:00'] Swift: UFL: DateFromStrUTCDemo [or StrToDateUTC][create a date (local time zone)][note: the functions can typically handle various date/time formats][note: can use 'Z' to treat strings as UTC (in many languages)] AutoHotkey: vDate := "20060504030201" C++: C#: Crystal: oDate = Time.parse_utc("2006-05-04 03:02:01", "%Y-%m-%d %H:%M:%S") Excel: ___ Excel VBA: ___ Go: Java: oDate = java.time.ZonedDateTime.parse("2006-05-04T03:02:01Z") JavaScript: oDate = new Date(Date.parse("2006-05-04 03:02:01 Z")) Kotlin: oDate = java.time.ZonedDateTime.parse("2006-05-04T03:02:01Z") PHP: $oDate = date_create_immutable("2006-05-04 03:02:01.999", timezone_open("UTC")) [e.g. $oDate = date_create_immutable("2006-05-04", timezone_open("UTC"))] Python: oDate = datetime.datetime.strptime("20060504030201 +00:00", "%Y%m%d%H%M%S %z") [also: oDate = datetime.datetime.strptime("20060504030201 +0000", "%Y%m%d%H%M%S %z")] [note: strptime(): '%z' can handle '+0100' and (since Python 3.7) '+01:00'] [note: strptime() doesn't support '%:z'] R: Ruby: oDate = Time.new("2006-05-04 03:02:01Z") Rust: Scala: oDate = java.time.ZonedDateTime.parse("2006-05-04T03:02:01Z") SQL (MySQL): ___ [e.g. timestamp('2006-05-04 03:02:01')] [e.g. timestamp('2006-05-04T03:02:01')] [e.g. timestamp('20060504030201')] [also: str_to_date('20060504030201', '%Y%m%d%H%i%S')] SQL (PostgreSQL): ___ [e.g. '2006-05-04 03:02:01+00' AT TIME ZONE 'UTC'] [note: no offset] [WARNING: converts date to local time without offset: '2006-05-04 03:02:01' AT TIME ZONE 'UTC'] SQL (SQLite): ___ [e.g. '2006-05-04 03:02:01'] [e.g. '2006-05-04T03:02:01'] [e.g. datetime('2006-05-04')] Swift: UFL: DateFromStrLocalDemo [or StrToDateLocal][create a date (local time zone)][note: the functions can typically handle various date/time formats] AutoHotkey: vDate := "20060504030201" C++: C#: Crystal: oDate = Time.parse_local("2006-05-04 03:02:01", "%Y-%m-%d %H:%M:%S") Excel: =DATEVALUE("2006-05-04 03:02:01")+TIMEVALUE("2006-05-04 03:02:01") Excel VBA: oDate = CDate("2006-05-04 03:02:01") [also: DateValue("2006-05-04 03:02:01") + TimeValue("2006-05-04 03:02:01")] Go: Java: oDate = java.time.ZonedDateTime.parse("2006-05-04T03:02:01+01:00[Europe/London]") JavaScript: oDate = new Date(Date.parse("2006-05-04 03:02:01")) Kotlin: oDate = java.time.ZonedDateTime.parse("2006-05-04T03:02:01+01:00[Europe/London]") PHP: $oDate = date_create_immutable("2006-05-04 03:02:01.999") [e.g. $oDate = date_create_immutable("2006-05-04")] Python: oDate = datetime.datetime.strptime("20060504030201", "%Y%m%d%H%M%S") [also: to always print the time zone: datetime.datetime.strptime("20060504030201", "%Y%m%d%H%M%S").astimezone()] R: Ruby: oDate = Time.new("2006-05-04 03:02:01") Rust: Scala: oDate = java.time.ZonedDateTime.parse("2006-05-04T03:02:01+01:00[Europe/London]") SQL (MySQL): ___ [can use (create local date, store it as UTC): e.g. convert_tz('2006-05-04 03:02:01', 'Europe/London', '+00:00')] [MAJOR WARNING: timestamp('2006-05-04T03:02:01+01:00'), it will create a UTC date and apply the offset, then convert to the *local* time zone, then store it as UTC, workaround: temporarily set the time zone to UTC] SQL (PostgreSQL): '2006-05-04 03:02:01'::timestamptz [also: timestamptz '2006-05-04 03:02:01'] SQL (SQLite): ___ [can use (create local date, store it as UTC): e.g. datetime('2006-05-04 03:02:01', 'utc')] [note: the 'utc' modifier 'assumes that the time-value to its left is in the local timezone and adjusts that time-value to be in UTC'] Swift: UFL: DateFromStrSpecificDemo [or StrToDateSpecific][create a date (specific time zone)][note: the functions can typically handle various date/time formats] AutoHotkey: ___ C++: C#: Crystal: oDate = Time.parse("2006-05-04 03:02:01", "%Y-%m-%d %H:%M:%S", Time::Location.load(vTZ)) [e.g. vTZ = "Europe/London"] Excel: ___ Excel VBA: ___ Go: Java: oDate = java.time.ZonedDateTime.parse("2006-05-04T03:02:01+01:00[Europe/London]") JavaScript: oDate = new Date(Date.parse("2006-05-04 03:02:01 +01:00")) Kotlin: oDate = java.time.ZonedDateTime.parse("2006-05-04T03:02:01+01:00[Europe/London]") PHP: $oDate = date_create_immutable("2006-05-04 03:02:01.999", timezone_open($vTZ)) [e.g. $oDate = date_create_immutable("2006-05-04", timezone_open($vTZ))] [e.g. $vTZ = "Europe/London"] Python: oDate = datetime.datetime.strptime("2006-05-04 03:02:01 +01:00", "%Y-%m-%d %H:%M:%S %z") [also: e.g. oDate = datetime.datetime.fromisoformat("2006-05-04T03:02:01+01:00")] [note: strptime(): '%z' can handle '+0100' and (since Python 3.7) '+01:00'] [note: strptime() doesn't support '%:z'] R: Ruby: ___ Rust: Scala: oDate = java.time.ZonedDateTime.parse("2006-05-04T03:02:01+01:00[Europe/London]") SQL (MySQL): ___ [can use (create date with specific time zone, store it as UTC): e.g. convert_tz('2006-05-04 03:02:01', 'Europe/London', '+00:00')] [MAJOR WARNING: timestamp('2006-05-04T03:02:01+01:00'), it will create a UTC date and apply the offset, then convert to the *local* time zone, then store it as UTC, workaround: temporarily set the time zone to UTC] SQL (PostgreSQL): '2006-05-04 03:02:01 +01:00'::timestamptz [WARNING: will create a timestamp with an offset, with the offset based on the *local* time zone] SQL (SQLite): ___ [can use (create date with offset, store it as UTC): e.g. datetime('2006-05-04 03:02:01 +01:00')] Swift: UFL: DateIsValid [validate date string][note: the functions can typically handle various date/time formats] AutoHotkey: vDateIsValid := IsTime(vDate) [e.g. vDateIsValid := IsTime("20060504030201")] [e.g. (also accepts ints): vDateIsValid := IsTime(20060504030201)] C++: C#: Crystal: ___ [note: Time.utc() throws if given invalid input] [e.g. oDate = Time.parse_utc("2006-05-04 03:02:01", "%Y-%m-%d %H:%M:%S")] Excel: ___ [e.g. =ISNUMBER(DATEVALUE("2006-05-04 03:02:01")+TIMEVALUE("2006-05-04 03:02:01"))] [WARNING: this is not 100% reliable, try changing one or more parts to '99' to see which pass/fail] Excel VBA: ___ [e.g. vDateIsValid = IsDate("2006-05-04 03:02:01")] Go: Java: ___ [note: parse() throws if given invalid input] [e.g. oDate = java.time.ZonedDateTime.parse("2006-05-04T03:02:01Z")] JavaScript: ___ [e.g. vDateIsValid = !isNaN(Date.parse("2006-05-04 03:02:01"))] Kotlin: ___ [note: parse() throws if given invalid input] [e.g. oDate = java.time.ZonedDateTime.parse("2006-05-04T03:02:01Z")] PHP: ___ [can use: $vDateIsValid = ($vDate == date("Y-m-d H:i:s", strtotime($vDate)))] [e.g. $vDate = "2006-05-04 03:02:01"] Python: ___ [note: datetime.datetime.strptime() throws if given invalid input] [e.g. oDate = datetime.datetime.strptime("2006-05-04 03:02:01", "%Y-%m-%d %H:%M:%S")] R: Ruby: ___ [note: Time.new() throws if given invalid input] [e.g. oDate = Time.new("2006-05-04 03:02:01")] Rust: Scala: ___ [note: parse() throws if given invalid input] [e.g. oDate = java.time.ZonedDateTime.parse("2006-05-04T03:02:01Z")] SQL (MySQL): timestamp(MyDate) IS NOT NULL [e.g. timestamp('2006-05-04 03:02:01') IS NOT NULL] SQL (PostgreSQL): ___ [WARNING: MyDateStr::timestamp throws if invalid] [can use (e.g. '2006-05-04 03:02:01'): to_char('0002-12-01 BC'::timestamp + ('P'||replace(MyDateStr, ' ', 'T'))::interval - '1 day'::interval, 'YYYY-MM-DD HH24:MI:SS') = MyDateStr] [also (e.g. '2006-05-04 03:02:01'): MyDateStr ~ '^\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}$'] [algorithm: date minus date-as-interval equals '0002-11-30 BC', the inverse isn't always true due to month addition logic, since month day counts vary (28/29/30/31), workaround: use '0002-12-01 BC' and afterwards subtract 1 day, e.g. '2000-01-01'::timestamp - 'P2000-01-01'::interval returns '0002-11-30 00:00:00 BC', however, '0002-11-30 BC'::timestamp + 'P2000-01-01'::interval returns 1999-12-31 00:00:00, 1 day too few] SQL (SQLite): datetime(MyDate) IS NOT NULL [e.g. datetime('2006-05-04 03:02:01') IS NOT NULL] Swift: UFL: DateFormatISOUTC [UTC time as ISO string, no time zone: '####-##-##T##:##:##.###Z' e.g. '2006-05-04T03:02:01.000Z'] AutoHotkey: vDate := FormatTime(vDate, "yyyy-MM-ddTHH:mm:ss.000Z") [note: assumes 0 milliseconds] C++: C#: Crystal: oDate.to_s("%Y-%m-%dT%H:%M:%S.%3NZ") Excel: ___ Excel VBA: ___ Go: Java: vDate = oDate.format(java.time.format.DateTimeFormatter.ofPattern("yyyy-MM-dd'T'HH:mm:ss.SSS'Z'")) JavaScript: vDate = oDate.toISOString() [note: the return value is in the UTC time zone (the local time zone is ignored)] [also: vDate = oDate.toJSON()] Kotlin: vDate = oDate.format(java.time.format.DateTimeFormatter.ofPattern("yyyy-MM-dd'T'HH:mm:ss.SSS'Z'")) PHP: $vDate = date_format($oDate, "Y-m-d H:i:s.v\Z") [note: use '\' for literal characters] Python: vDate = oDate.astimezone(datetime.timezone.utc).strftime("%Y-%m-%dT%H:%M:%S.%f")[:-3]+"Z" [also: oDate.astimezone(datetime.timezone.utc).isoformat(timespec="milliseconds")[:23] + "Z"] [also: "%FT%T.%f", F%/T% aren't listed on the Python documentation] R: Ruby: oDate.strftime("%Y-%m-%dT%H:%M:%S.%LZ") [also: oDate.xmlschema(3)] [alias (xmlschema): iso8601] Rust: Scala: vDate = oDate.format(java.time.format.DateTimeFormatter.ofPattern("yyyy-MM-dd'T'HH:mm:ss.SSS'Z'")) SQL (MySQL): ___ [can use: insert(date_format(MyDate, '%Y-%m-%dT%H:%i:%S.%fZ'), 24, 3, '')] [note: use insert() to remove the microseconds] SQL (PostgreSQL): to_char(MyDate, 'YYYY-MM-DD"T"HH24:MI:SS.MSZ') [e.g. to_char('2006-05-04 03:02:01+00'::timestamp, 'YYYY-MM-DD"T"HH24:MI:SS.MSZ')] SQL (SQLite): strftime('%Y-%m-%dT%H:%M:%fZ', MyDate) Swift: UFL: DateFormatISOLocal [local time as ISO string, no time zone: '####-##-##T##:##:##.###Z' e.g. '2006-05-04T03:02:01.000Z'][this function is not intended to incorrectly present local dates as UTC, but to display the date in a readable format, with the intention of removing the trailing 'Z'][note: includes milliseconds] AutoHotkey: vDate := FormatTime(vDate, "yyyy-MM-ddTHH:mm:ss.000Z") [WARNING: prints 0 milliseconds] C++: C#: Crystal: oDate.to_s("%Y-%m-%dT%H:%M:%S.%3NZ") Excel: =TEXT(A1,"yyyy-mm-ddThh:mm:ss.000Z") [note: 'ss.000' *does* handle milliseconds (the Excel sheet function prints milliseconds, Excel VBA prints '000')] Excel VBA: ___ [can use: vDate = Format(oDate, "yyyy-mm-ddThh:mm:ss.") & Right(Round((oDate - Int(oDate)) * 86400000), 3) & "Z"] [also (prints 0 milliseconds): vDate = Format(oDate, "yyyy-mm-ddThh:mm:ss.000Z") [WARNING: 'ss.000' prints 0 milliseconds (the Excel sheet function prints milliseconds, Excel VBA prints '000')] Go: Java: vDate = oDate.format(java.time.format.DateTimeFormatter.ofPattern("yyyy-MM-dd'T'HH:mm:ss.SSS'Z'")) JavaScript: vDate = new Date(oDate.valueOf()-oDate.getTimezoneOffset()*60000).toISOString() [note: valueOf()/getTime() are interchangeable] Kotlin: vDate = oDate.format(java.time.format.DateTimeFormatter.ofPattern("yyyy-MM-dd'T'HH:mm:ss.SSS'Z'")) PHP: $vDate = date_format($oDate, "Y-m-d H:i:s.v\Z") Python: vDate = oDate.astimezone().strftime("%Y-%m-%dT%H:%M:%S.%f")[:-3]+"Z" [also: oDate.astimezone().isoformat(timespec="milliseconds")[:23] + "Z"] [also: "%FT%T.%f", F%/T% aren't listed on the Python documentation] R: Ruby: oDate.strftime("%Y-%m-%dT%H:%M:%S.%LZ") [also: oDate.xmlschema(3)] [alias (xmlschema): iso8601] Rust: Scala: vDate = oDate.format(java.time.format.DateTimeFormatter.ofPattern("yyyy-MM-dd'T'HH:mm:ss.SSS'Z'")) SQL (MySQL): ___ [can use (convert UTC to local then print): insert(date_format(convert_tz(MyDate, '+00:00', '+01:00'), '%Y-%m-%dT%H:%i:%S.%fZ'), 24, 3, '')] [note: use insert() to remove the microseconds] SQL (PostgreSQL): to_char(MyDate, 'YYYY-MM-DD"T"HH24:MI:SS.MSZ') [e.g. to_char('2006-05-04 03:02:01+01'::timestamp, 'YYYY-MM-DD"T"HH24:MI:SS.MSZ')] [e.g. to_char('2006-05-04 03:02:01'::timestamp, 'YYYY-MM-DD"T"HH24:MI:SS.MSZ')] SQL (SQLite): strftime('%Y-%m-%dT%H:%M:%fZ', MyDate, 'localtime') Swift: UFL: DateFormat14 [14-digit string (yyyyMMddHHmmss)] AutoHotkey: vDate := FormatTime(vDate, "yyyyMMddHHmmss") C++: C#: Crystal: vDate = oDate.to_s("%Y%m%d%H%M%S") Excel: =TEXT(A1,"yyyymmddhhmmss") [WARNING: 'mm' returns months/minutes depending on surrounding chars] Excel VBA: vDate = Format(oDate, "yyyymmddhhmmss") [also: Format(oDate, "yyyymmddhhnnss")] [WARNING: 'mm' returns months/minutes depending on surrounding chars] Go: Java: vDate = oDate.format(java.time.format.DateTimeFormatter.ofPattern("yyyyMMddHHmmss")) JavaScript: vDate = oDate.toISOString().replace(/\D+/g, "").slice(0, 14) [note: returns UTC time] [also (fairly similar): (using af/lt/sv/en-CA-u-hc-h23 locales): oDate.toLocaleString("af", {timeZone:"UTC"})] [note: '-u-hc-h23' means use 24-hour time] [note: using locales is best avoided as formats sometimes change] Kotlin: vDate = oDate.format(java.time.format.DateTimeFormatter.ofPattern("yyyyMMddHHmmss")) PHP: $vDate = date_format($oDate, "Y-m-d H:i:s") Python: vDate = oDate.strftime("%Y%m%d%H%M%S") R: Ruby: vDate = oDate.strftime("%Y%m%d%H%M%S") Rust: Scala: vDate = oDate.format(java.time.format.DateTimeFormatter.ofPattern("yyyyMMddHHmmss")) SQL (MySQL): date_format(MyDate, '%Y%m%d%H%i%S') [also: date_format(MyDate, get_format(DATETIME, 'internal'))] SQL (PostgreSQL): to_char(MyDate, 'YYYYMMDDHH24MISS') SQL (SQLite): strftime('%Y%m%d%H%M%S', MyDate) Swift: UFL: DateSplit [6-item array e.g. string (yyyy,MM,dd,HH,mm,ss) or numeric (yyyy,M,d,H,m,s)] AutoHotkey: oArray := StrSplit(FormatTime(vDate, "yyyy|MM|dd|HH|mm|ss"), "|") [note: will preserve any leading zeros] C++: C#: Crystal: oArray = oDate.to_s("%Y|%m|%d|%H|%M|%S").split("|") Excel: ___ [note: can use: TEXT(A1,"yyyy|mm|dd|hh|mm|ss")] Excel VBA: oArray = Split(Format(oDate, "yyyy|mm|dd|hh|mm|ss"), "|") Go: Java: oArray = oDate.format(java.time.format.DateTimeFormatter.ofPattern("yyyy|MM|dd|HH|mm|ss")).split("\\|") JavaScript: oArray = oDate.toISOString().split(/\D+/g, 6) [note: returns UTC time] Kotlin: oArray = oDate.format(java.time.format.DateTimeFormatter.ofPattern("yyyy|MM|dd|HH|mm|ss")).split("|") PHP: $oArray = explode("|", date_format($oDate, "Y|m|d|H|i|s")) Python: oList = str.split(oDate.strftime("%Y|%m|%d|%H|%M|%S"), "|") R: Ruby: oArray = oDate.strftime("%Y|%m|%d|%H|%M|%S").split("|") [also: oDate.to_a returns '[sec, min, hour, day, mon, year, wday, yday, dst?, zone]'] Rust: Scala: oArray = oDate.format(java.time.format.DateTimeFormatter.ofPattern("yyyy|MM|dd|HH|mm|ss")).split("\\|") SQL (MySQL): ___ SQL (PostgreSQL): string_to_array(to_char(MyDate, 'YYYY|MM|DD|HH24|MI|SS'), '|') SQL (SQLite): ___ [can use (0-based): e.g. strftime('["%Y","%m","%d","%H","%M","%S"]', 'now') ->> 3] [WARNING: int '03', with a leading zero, is considered 'malformed JSON', so we use string '"03"' instead] Swift: UFL: DateClone [or DateCopy][copy the date object (date with time zone, else UTC date, else date without time zone)][see also: OpEquals/Any.Address] AutoHotkey: vDateNew := vDate C++: C#: Crystal: oDateNew = oDate.clone Excel: ___ [can use: =A1] Excel VBA: oDateNew = oDate [also: oDateNew = oDate + 0] [also: oDateNew = CDate(oDate)] [note: can use VarPtr(oObj) to confirm a clone, not a reference] Go: Java: ___ [can use: oDateNew = oDate.plusSeconds(0)] [note: 'oDateNew = oDate' creates a reference, not a clone, however, date objects are immutable, so this should be safe to treat as a clone] [note: 'oDate1 == oDate2' returns true for a reference, but false for a clone] JavaScript: oDateNew = new Date(oDate) Kotlin: ___ [can use: oDateNew = oDate.plusSeconds(0)] [note: 'oDateNew = oDate' creates a reference, not a clone, however, date objects are immutable, so this should be safe to treat as a clone] [note: 'oDate1 === oDate2' returns true for a reference, but false for a clone] PHP: $oDateNew = clone $oDate Python: ___ [can use: oDateNew = oDate + datetime.timedelta(seconds=0)] [note: 'oDateNew = oDate' creates a reference, not a clone, however, date objects are immutable, so this should be safe to treat as a clone] [note: 'oDate1 is oDate2' returns true for a reference, but false for a clone] R: Ruby: oDateNew = oDate.clone [note: 'oDate1.equal?(oDate2)' returns true for a reference, but false for a clone] Rust: Scala: ___ [can use: oDateNew = oDate.plusSeconds(0)] [note: 'oDateNew = oDate' creates a reference, not a clone, however, date objects are immutable, so this should be safe to treat as a clone] [note: 'oDate1 eq oDate2' returns true for a reference, but false for a clone] SQL (MySQL): ___ SQL (PostgreSQL): ___ SQL (SQLite): ___ Swift: UFL: DateAdd [or DateAddPart][e.g. add days/hours/minutes/seconds/milliseconds] AutoHotkey: vDateNew := DateAdd(vDate, vNum, vUnit) [e.g. units: D/H/M/S, doesn't handle milliseconds] [e.g. add 3 days: vDateNew := DateAdd(vDate, 3, "D")] C++: C#: Crystal: ___ [e.g. add 3 days: oDateNew = oDate + Time::Span.new(days:3)] Excel: =A1+vDays [e.g. add 3 days: =A1+3] [e.g. add seconds: =A1+vSec/86400] [e.g. add 12 hours: =A1+0.5] Excel VBA: oDateNew = DateAdd(vUnit, vNum, oDate) [e.g. add 3 days: oDateNew = DateAdd("d", 3, oDate)] [e.g. units: yyyy/m/d/h/n/s, also: q (quarter = 3 months), ww (week), y/w (day)] [WARNING: y (day)] [WARNING: w (day), for DateDiff w is weeks] Go: Java: oDateNew = oDate.plus(vNum, oUnit) [e.g. oDateNew = oDate.plus(vDays, java.time.temporal.ChronoUnit.DAYS)] [note: plus() uses 'long, TemporalUnit', with() uses 'TemporalField, long'] JavaScript: oDateNew = new Date(oDate.valueOf() + vMSec) [e.g. add 3 days: oDateNew = new Date(oDate.valueOf() + 3*86400*1000)] [note: valueOf()/getTime() are interchangeable, there is no 'getUTCTime', since getTime() looks like other 'getXXX' methods such as getDate()/getUTCDate(), valueOf() is preferable] Kotlin: oDateNew = oDate.plus(vNum.toLong(), oUnit) [e.g. oDateNew = oDate.plus(vDays.toLong(), java.time.temporal.ChronoUnit.DAYS)] [note: plus() uses 'long, TemporalUnit', with() uses 'TemporalField, long'] PHP: ___ [e.g. add 3 days: $oDateNew = $oDate->modify("3 days")] Python: ___ [e.g. add 3 days: oDateNew = oDate + datetime.timedelta(days=3)] [note: datetime.timedelta(days=0, seconds=0, microseconds=0, milliseconds=0, minutes=0, hours=0, weeks=0)] [note: any argument can be omitted] R: Ruby: ___ [e.g. add 3 days: oDateNew = oDate + 3*86400] Rust: Scala: oDateNew = oDate.plus(vNum.toLong, oUnit) [e.g. oDateNew = oDate.plus(vDays.toLong, java.time.temporal.ChronoUnit.DAYS)] [note: plus() uses 'long, TemporalUnit', with() uses 'TemporalField, long'] SQL (MySQL): ___ [e.g. timestampadd(MyUnit, MyNum, MyDate)] [also: adddate()/addtime()/date_add(), date_sub()/subdate()/subtime()] [related: timestamp(), period_add()] SQL (PostgreSQL): ___ [e.g. add 3 days: date_add('2006-05-04 03:02:01'::timestamp, '3 days'::interval)] [also (add 3 days): '2006-05-04 03:02:01'::timestamptz + '3 days'::interval] SQL (SQLite): ___ [e.g. add 3 days: datetime(MyDate, '3 days')] [e.g. units (the trailing 's' can be omitted): years/months/days/hours/minutes/seconds] [note: multiple such strings can be used in one function call] Swift: UFL: DateAddYears [2 common algorithms: set the year value, if the day is too big for the month by n days (29/30/31), either, use the nth day from the next month (ceiling), or use the largest valid day (equivalent to subtracting n days) (floor)][WARNING: ceiling algorithm: adding/subtracting years to/from 29th Feb, can result in 1st Mar (increasing the month value)][note: if the original day is between 1-28 inclusive, there is no ambiguity][e.g. ceiling algorithm: 2000-02-29 + 1 year -> 2001-02-29 (invalid) -> 2001-02-28 + 1 -> 2001-03-01] AutoHotkey: ___ C++: C#: Crystal: oDateNew = oDate.shift(years:vYears) [also: oDateNew = oDate + 3.years] [note: uses floor algorithm] Excel: ___ [can use: =EDATE(A1,vYears*12)] [note: uses floor algorithm] Excel VBA: oDateNew = DateAdd("yyyy", vYears, oDate) [note: uses floor algorithm] Go: Java: oDateNew = oDate.plusYears(vYears) [note: uses floor algorithm] JavaScript: ___ [can use (floor algorithm): oDateNew = new Date(oDate); oDateNew.setUTCMonth(oDate.getUTCMonth() + vYears*12); oDateNew = new Date(oDateNew.valueOf() - (oDateNew.getUTCDate() == oDate.getUTCDate() ? 0 : oDateNew.getUTCDate()*86400000));] Kotlin: oDateNew = oDate.plusYears(vYears.toLong()) [note: uses floor algorithm] PHP: ___ [can use: $oDateNew = $oDate->modify($vYears . " years"); $oDateNew = $oDateNew->modify($oDateNew->format("d") == $oDate->format("d") ? "0 days" : "-" . $oDateNew->format("d") . "days");] Python: ___ [WARNING: this throws if date would be invalid: oDateNew = oDate.replace(year=oDate.year+vYears)] R: Ruby: ___ [can use (floor algorithm): oArray = oDate.to_a; oArray[5] += vYears; oDateNew = Time.utc(*oArray); oDateNew -= (oDateNew.mday == oDate.mday) ? 0 : oDateNew.mday*86400] [note: truncated division] [note: can replace 'oDateNew.mday*86400' with '86400' if adding years, but not if adding months] Rust: Scala: oDateNew = oDate.plusYears(vYears.toLong) [note: uses floor algorithm] SQL (MySQL): ___ [e.g. add 3 years: timestampadd(YEAR, 3, MyDate)] [note: uses floor algorithm: 'When adding a MONTH ... day that does not exist in the given month, the day is adjusted to the last day of the month'] SQL (PostgreSQL): ___ [e.g. date_add('2006-05-04 03:02:01'::timestamp, '3 years'::interval)] [also: '2006-05-04 03:02:01'::timestamptz + '3 years'::interval] [note: uses floor algorithm] SQL (SQLite): ___ [e.g. 'floor' mode: add 3 years: datetime(MyDate, '3 years', 'floor')] [e.g. 'floor' mode: add 1 year: datetime(MyDate, '1 year', 'floor')] [e.g. 'ceiling' mode: add 3 years: datetime(MyDate, '3 years')] [WARNING ('ceiling' mode): adding 1 month, can increase the month by 2] Swift: UFL: DateAddMonths [2 common algorithms: set the year and/or month values, if the day is too big for the month by n days (29/30/31), either, use the nth day from the next month (ceiling), or use the largest valid day (equivalent to subtracting n days) (floor)][WARNING: ceiling algorithm: 2000-05-31 - 1 month = 2000-05-01 (month value unchanged), 2000-05-31 + 1 month = 2000-07-01 (month value increased by 2)][note: if the original day is between 1-28 inclusive, there is no ambiguity][e.g. ceiling algorithm: 2001-01-31 + 1 month -> 2001-02-31 (invalid) -> 2001-02-28 + 3 -> 2001-03-03] AutoHotkey: ___ C++: C#: Crystal: oDateNew = oDate.shift(months:vMonths) [also: oDateNew = oDate + 3.months] [note: uses floor algorithm] Excel: =EDATE(A1,vMonths) [note: uses floor algorithm] Excel VBA: oDateNew = DateAdd("m", vMonths, oDate) [note: uses floor algorithm] Go: Java: oDateNew = oDate.plusMonths(vMonths) [note: uses floor algorithm] JavaScript: ___ [can use (floor algorithm): oDateNew = new Date(oDate); oDateNew.setUTCMonth(oDate.getUTCMonth() + vMonths); oDateNew = new Date(oDateNew.valueOf() - (oDateNew.getUTCDate() == oDate.getUTCDate() ? 0 : oDateNew.getUTCDate()*86400000));] [WARNING: uses ceiling algorithm: 'if the current value is 31st January 2016, calling setMonth with a value of 1 will return 2nd March 2016', i.e. 2016-01-31 + 1 month -> 2016-02-31 (invalid) -> 2016-02-29 + 2 -> 2016-03-02] Kotlin: oDateNew = oDate.plusMonths(vMonths.toLong()) [note: uses floor algorithm] PHP: ___ [can use: $oDateNew = $oDate->modify($vMonths . " months"); $oDateNew = $oDateNew->modify($oDateNew->format("d") == $oDate->format("d") ? "0 days" : "-" . $oDateNew->format("d") . "days");] Python: ___ [WARNING: this throws if date would be invalid: oDateNew = oDate.replace(month=oDate.month+vMonths)] R: Ruby: ___ [can use (floor algorithm): oArray = oDate.to_a; oArray[5] = (oDate.year*12+oDate.month-1+vMonths)/12; oArray[4] = (oDate.year*12+oDate.month-1+vMonths)%12+1; oDateNew = Time.utc(*oArray); oDateNew -= (oDateNew.mday == oDate.mday) ? 0 : oDateNew.mday*86400] [note: truncated division] Rust: Scala: oDateNew = oDate.plusMonths(vMonths.toLong) [note: uses floor algorithm] SQL (MySQL): ___ [e.g. add 3 months: timestampadd(MONTH, 3, MyDate)] [note: uses floor algorithm: 'When adding a MONTH ... day that does not exist in the given month, the day is adjusted to the last day of the month'] SQL (PostgreSQL): ___ [e.g. date_add('2006-05-04 03:02:01'::timestamp, '3 months'::interval)] [also: '2006-05-04 03:02:01'::timestamptz + '3 months'::interval] [note: uses floor algorithm] SQL (SQLite): ___ [e.g. 'floor' mode: add 3 months: datetime(MyDate, '3 months', 'floor')] [e.g. 'ceiling' mode: add 3 months: datetime(MyDate, '3 months')] Swift: UFL: DateAddDays [date add days] AutoHotkey: vDateNew := DateAdd(vDate, vNum, "D") C++: C#: Crystal: oDateNew = oDate + Time::Span.new(days:vDays) [also: oDateNew = oDate + 3.days] Excel: =A1+vDays [e.g. add 3 days: =A1+3] Excel VBA: oDateNew = DateAdd("d", vDays, oDate) Go: Java: oDateNew = oDate.plusDays(vDays) JavaScript: oDateNew = new Date(oDate.valueOf() + vDays*86400*1000) Kotlin: oDateNew = oDate.plusDays(vDays.toLong()) PHP: $oDateNew = $oDate->modify($vDays . " days") Python: oDateNew = oDate + datetime.timedelta(days=vDays) R: Ruby: oDateNew = oDate + vDays*86400 Rust: Scala: oDateNew = oDate.plusDays(vDays.toLong) SQL (MySQL): ___ [e.g. add 3 days: timestampadd(DAY, 3, MyDate)] SQL (PostgreSQL): ___ [e.g. date_add('2006-05-04 03:02:01'::timestamp, '3 days'::interval)] [also: '2006-05-04 03:02:01'::timestamptz + '3 days'::interval] SQL (SQLite): ___ [e.g. add 3 days: datetime(MyDate, '3 days')] Swift: UFL: DateAddHours [date add hours] AutoHotkey: vDateNew := DateAdd(vDate, vNum, "H") C++: C#: Crystal: oDateNew = oDate + Time::Span.new(hours:vHours) [also: oDateNew = oDate + 3.hours] Excel: =A1+vHours/24 [e.g. add 3 hours: =A1+3/24] [e.g. add 12 hours: =A1+0.5] [can use (if hours in range 0-23): =A1+TIME(vHours,0,0)] Excel VBA: oDateNew = DateAdd("h", vHours, oDate) Go: Java: oDateNew = oDate.plusHours(vHours) JavaScript: oDateNew = new Date(oDate.valueOf() + vHours*1440*1000) Kotlin: oDateNew = oDate.plusHours(vHours.toLong()) PHP: $oDateNew = $oDate->modify($vHours . " hours") Python: oDateNew = oDate + datetime.timedelta(hours=vHours) R: Ruby: oDateNew = oDate + vHours*3600 Rust: Scala: oDateNew = oDate.plusHours(vHours.toLong) SQL (MySQL): ___ [e.g. add 3 hours: timestampadd(HOUR, 3, MyDate)] SQL (PostgreSQL): ___ [e.g. date_add('2006-05-04 03:02:01'::timestamp, '3 hours'::interval)] [also: '2006-05-04 03:02:01'::timestamptz + '3 hours'::interval] SQL (SQLite): ___ [e.g. add 3 hours: datetime(MyDate, '3 hours')] Swift: UFL: DateAddMinutes [date add minutes] AutoHotkey: vDateNew := DateAdd(vDate, vNum, "M") C++: C#: Crystal: oDateNew = oDate + Time::Span.new(minutes:vMin) [also: oDateNew = oDate + 3.minutes] Excel: =A1+vMin/1440 [e.g. add 3 minutes: =A1+3/1440] [can use (if minutes in range 0-59): =A1+TIME(0,vMin,0)] Excel VBA: oDateNew = DateAdd("n", vMin, oDate) Go: Java: oDateNew = oDate.plusMinutes(vMin) JavaScript: oDateNew = new Date(oDate.valueOf() + vMin*60*1000) Kotlin: oDateNew = oDate.plusMinutes(vMin.toLong()) PHP: $oDateNew = $oDate->modify($vMin . " mins") Python: oDateNew = oDate + datetime.timedelta(minutes=vMin) R: Ruby: oDateNew = oDate + vMin*60 Rust: Scala: oDateNew = oDate.plusMinutes(vMin.toLong) SQL (MySQL): ___ [e.g. add 3 minutes: timestampadd(MINUTE, 3, MyDate)] SQL (PostgreSQL): ___ [e.g. date_add('2006-05-04 03:02:01'::timestamp, '3 minutes'::interval)] [also: '2006-05-04 03:02:01'::timestamptz + '3 minutes'::interval] SQL (SQLite): ___ [e.g. add 3 minutes: datetime(MyDate, '3 minutes')] Swift: UFL: DateAddSeconds [date add seconds] AutoHotkey: vDateNew := DateAdd(vDate, vNum, "S") C++: C#: Crystal: oDateNew = oDate + Time::Span.new(seconds:vSec) [also: oDateNew = oDate + 3.seconds] Excel: =A1+vSec/86400 [e.g. add 3 seconds: =A1+3/86400] [can use (if seconds in range 0-59): =A1+TIME(0,0,vSec)] Excel VBA: oDateNew = DateAdd("s", vSec, oDate) Go: Java: oDateNew = oDate.plusSeconds(vSec) JavaScript: oDateNew = new Date(oDate.valueOf() + vSec*1000) Kotlin: oDateNew = oDate.plusSeconds(vSec.toLong()) PHP: $oDateNew = $oDate->modify($vSec . " secs") Python: oDateNew = oDate + datetime.timedelta(seconds=vSec) R: Ruby: oDateNew = oDate + vSec Rust: Scala: oDateNew = oDate.plusSeconds(vSec.toLong) SQL (MySQL): ___ [e.g. add 3 seconds: timestampadd(SECOND, 3, MyDate)] SQL (PostgreSQL): ___ [e.g. date_add('2006-05-04 03:02:01'::timestamp, '3 seconds'::interval)] [also: '2006-05-04 03:02:01'::timestamptz + '3 seconds'::interval] SQL (SQLite): ___ [e.g. add 3 seconds: datetime(MyDate, '3 seconds')] Swift: UFL: DateAddMilliseconds [or DateAddMSec][date add milliseconds] AutoHotkey: ___ C++: C#: Crystal: oDateNew = oDate + Time::Span.new(nanoseconds:vMSec*1000000) Excel: =A1+vMSec/(86400*1000) [e.g. add 3 milliseconds: =A1+3/(86400*1000)] Excel VBA: oDateNew = oDate + vMSec / (86400 * 1000) Go: Java: oDateNew = oDate.plusNanos(vMSec*1000000) [also: oDateNew = oDate.plus(vMSec, java.time.temporal.ChronoUnit.MILLIS)] JavaScript: oDateNew = new Date(oDate.valueOf() + vMSec) Kotlin: oDateNew = oDate.plusNanos(vMSec.toLong()*1000000) [also: oDateNew = oDate.plus(vMSec.toLong(), java.time.temporal.ChronoUnit.MILLIS)] PHP: $oDateNew = $oDate->modify($vMSec . " msecs") Python: oDateNew = oDate + datetime.timedelta(milliseconds=vMSec) R: Ruby: oDateNew = oDate + vMSec/1000.0 Rust: Scala: oDateNew = oDate.plusNanos(vMSec.toLong*1000000) [also: oDateNew = oDate.plus(vMSec.toLong, java.time.temporal.ChronoUnit.MILLIS)] SQL (MySQL): ___ [e.g. add 3 milliseconds: timestampadd(MICROSECOND, 3000, MyDate)] SQL (PostgreSQL): ___ [e.g. date_add('2006-05-04 03:02:01'::timestamp, '3 milliseconds'::interval)] [also: '2006-05-04 03:02:01'::timestamptz + '3 milliseconds'::interval] SQL (SQLite): ___ [e.g. datetime(MyDate, '+0000-00-00 00:00:00.123', 'subsec')] [note: 'subsec' is to display the result, it doesn't affect the calculations] Swift: UFL: DateAddWorkdays [add working days to current date, specify a list of dates to exclude] AutoHotkey: ___ C++: C#: Crystal: ___ Excel: =A1-INT(A1)+WORKDAY(A1,vDays) [also: MOD(A1,1)+WORKDAY(A1,vDays)] [note: or for the date only (with no time value): WORKDAY(A1,vDays)] Excel VBA: oDateNew = TimeValue(oDate) + WorksheetFunction.WorkDay(oDate, vDays) [note: or for the date only (with no time value): CDate(WorksheetFunction.WorkDay(oDate, vDays))] Go: Java: ___ JavaScript: ___ Kotlin: ___ PHP: $oDateNew = $oDate->modify($vDays . " weekdays") Python: ___ R: Ruby: ___ Rust: Scala: ___ SQL (MySQL): ___ SQL (PostgreSQL): ___ SQL (SQLite): ___ Swift: UFL: DateDiffFriendly [or DateFormatRelative/DateDiffRelative][show a relative date, e.g. 'in 3 days'/'3 days ago'][see also: PrintShowMoreDigits] AutoHotkey: ___ C++: C#: Crystal: ___ [can use: (oDate1-oDate2).to_s] [e.g. 3 days: '3.00:00:00'] [e.g. 3 milliseconds: '0.00:00:00.003000000'] Excel: ___ Excel VBA: ___ Go: Java: ___ JavaScript: ___ [e.g. 'in 3 days': oFormat.format(3, "day")] [e.g. '3 days ago': oFormat.format(-3, "day")] [also: e.g. oFormat = new Intl.RelativeTimeFormat("en", {style:"short"})] Kotlin: ___ PHP: ___ [can use: $oDate2->diff($oDate1)->format("%r%a.%H:%I:%S")] [e.g. 3 days: '3.00:00:00'] [WARNING: $oDate1->diff($oDate2) returns $oDate2-$oDate1 (invert it via '-' for the more intuitive/common $oDate1-$oDate2)] Python: ___ R: Ruby: ___ Rust: Scala: ___ SQL (MySQL): ___ SQL (PostgreSQL): age(MyDate1, MyDate2) [related: justify_days()/justify_hours()/justify_interval()] SQL (SQLite): ___ [can use: timediff(MyDate1, MyDate2)] [note: timediff() returns output of the form '(+|-)YYYY-MM-DD HH:MM:SS.SSS'] Swift: UFL: DateDiff [or DateDiffPart][e.g. difference in days/hours/minutes/seconds/milliseconds][MAJOR WARNING: diff functions are typically: diff(a,b) == a-b, consistent with cmp(a,b) == sign(a-b), but some languages use b-a][e.g. compare '2000-01-04 05:06:07' and '2000-01-01'][WARNING: when comparing dates, consider DST][WARNING: differences can be positive or negative, so use truncated division, not floor division] AutoHotkey: vDiff := DateDiff(vDate1, vDate2, vUnit) [e.g. units: D/H/M/S, doesn't handle milliseconds] [e.g. days: vDiff := DateDiff(vDate1, vDate2, "D")] C++: C#: Crystal: ___ [e.g. days: vDiff = (oDate1-oDate2).total_days.to_i] Excel: ___ [e.g. days: =TRUNC(A1-B1)] [WARNING: gives incorrect values for Jan 1900 and Feb 1900 (e.g. 1900-03-01 minus 1900-02-28 is actually 1 day)] Excel VBA: vDiff = -DateDiff(vUnit, oDate1, oDate2) [e.g. days: vDiff = -DateDiff("d", oDate1, oDate2)] [MAJOR WARNING: DateDiff returns oDate2-oDate1 (invert it via '-' for the more intuitive/common oDate1-oDate2)] [note: DateDiff gives correct values for Jan 1900 and Feb 1900] [e.g. units: yyyy/m/d/h/n/s, also: q (quarter = 3 months), w (week), ww (calendar week), y (day)] [WARNING: y (day)] [WARNING: w (week), for DateAdd w is days] Go: Java: ___ [e.g. days: vDiff = -java.time.temporal.ChronoUnit.DAYS.between(oDate1, oDate2)] [WARNING: between() returns oDate2-oDate1 (invert it via '-' for the more intuitive/common oDate1-oDate2)] JavaScript: ___ [e.g. days: vDiff = Math.trunc((oDate1.valueOf()-oDate2.valueOf())/86400000)] [note: valueOf()/getTime() are interchangeable] Kotlin: ___ [e.g. days: vDiff = -java.time.temporal.ChronoUnit.DAYS.between(oDate1, oDate2)] [WARNING: between() returns oDate2-oDate1 (invert it via '-' for the more intuitive/common oDate1-oDate2)] PHP: ___ [e.g. days: $vDiff = intdiv($oDate1->getTimestamp()-$oDate2->getTimestamp(), 86400)] Python: ___ [e.g. days: vDiff = int((oDate1-oDate2).total_seconds()/86400)] [note: oDate1-oDate2 creates a timedelta object] [note: total_seconds() returns seconds and fractions of seconds] R: Ruby: ___ [e.g. days: vDiff = ((oDate1-oDate2)/86400).to_i] Rust: Scala: ___ [e.g. days: vDiff = -java.time.temporal.ChronoUnit.DAYS.between(oDate1, oDate2)] [WARNING: between() returns oDate2-oDate1 (invert it via '-' for the more intuitive/common oDate1-oDate2)] SQL (MySQL): -timestampdiff(MyUnit, MyDate1, MyDate2) [WARNING: timestampdiff() returns oDate2-oDate1 (invert it via '-' for the more intuitive/common oDate1-oDate2)] [also: datediff(), period_diff(), timediff()] SQL (PostgreSQL): ___ [e.g. days: (extract(epoch from age(MyDate1, MyDate2)) / 86400)::int] SQL (SQLite): ___ [e.g. days: trunc(julianday(MyDate1)-julianday(MyDate2))] [also: e.g. days: trunc((unixepoch(MyDate1)-unixepoch(MyDate2))/86400)] [also (friendly format): timediff(MyDate1, MyDate2)] Swift: UFL: DateDiffYears [count complete years (e.g. tiebreaks: Year1>Year2 but Month1<Month2, return vYear1-vYear2-1)] AutoHotkey: ___ [can use (assumes both dates are 4 digits or more, and of equal length): vDiffRaw := SubStr(vDate1, 1, 4)-SubStr(vDate2, 1, 4), vCmpRest := StrCompare(SubStr(vDate1, 5), SubStr(vDate2, 5)), vDiff := vDiffRaw + ((vDiffRaw>0)-(vDiffRaw<0) == -vCmpRest ? vCmpRest : 0)] [note: no sign function, so uses inequality workaround] C++: C#: Crystal: ___ [can use: vDiffRaw = oDate1.year-oDate2.year; vCmpRest = oDate1.to_s("%m%d%T%9N")<=>oDate2.to_s("%m%d%T%9N"); vDiff = vDiffRaw + ((vDiffRaw<=>0) == -vCmpRest ? vCmpRest : 0)] [note: no sign function, so uses 'vNum<=>0' workaround] Excel: ___ Excel VBA: ___ [can use: vDiffRaw = -DateDiff("yyyy", oDate1, oDate2): vCmpRest = StrComp(Format(oDate1, "mm-dd hh:mm:ss"), Format(oDate2, "mm-dd hh:mm:ss")): vDiff = vDiffRaw + IIf(Sgn(vDiffRaw) = -vCmpRest, vCmpRest, 0)] Go: Java: vDiff = -java.time.temporal.ChronoUnit.YEARS.between(oDate1, oDate2) [WARNING: between() returns oDate2-oDate1 (invert it via '-' for the more intuitive/common oDate1-oDate2)] JavaScript: ___ [can use: vDiffRaw = oDate1.getUTCFullYear()-oDate2.getUTCFullYear(); vCmpRest = +(oDate1.toISOString().slice(5)>oDate2.toISOString().slice(5))||-(oDate1.toISOString().slice(5)<oDate2.toISOString().slice(5)); vDiff = vDiffRaw + (Math.sign(vDiffRaw) == -vCmpRest ? vCmpRest : 0);] Kotlin: vDiff = -java.time.temporal.ChronoUnit.YEARS.between(oDate1, oDate2) [WARNING: between() returns oDate2-oDate1 (invert it via '-' for the more intuitive/common oDate1-oDate2)] PHP: ___ [can use: $vDiff = -$oDate1->diff($oDate2)->format("%r%Y")] [WARNING: $oDate1->diff($oDate2) returns $oDate2-$oDate1 (invert it via '-' for the more intuitive/common $oDate1-$oDate2)] Python: ___ [can use: vDiffRaw = oDate1.year-oDate2.year; vCmpRest = +(oDate1.strftime("%m%d%T%f")>oDate2.strftime("%m%d%T%f")) or -(oDate1.strftime("%m%d%T%f")<oDate2.strftime("%m%d%T%f")); vDiff = vDiffRaw + (vCmpRest if (math.copysign(1 if vDiffRaw else 0, vDiffRaw) == -vCmpRest) else 0)] [note: no sign function, so uses copysign workaround] [note: if dates not UTC, use '.astimezone(datetime.timezone.utc)'] R: Ruby: ___ [can use: vDiffRaw = oDate1.year-oDate2.year; vCmpRest = oDate1.strftime("%m%d%T%f")<=>oDate2.strftime("%m%d%T%f"); vDiff = vDiffRaw + ((vDiffRaw<=>0) == -vCmpRest ? vCmpRest : 0)] [note: no sign function, so uses 'vNum<=>0' workaround] Rust: Scala: vDiff = -java.time.temporal.ChronoUnit.YEARS.between(oDate1, oDate2) [WARNING: between() returns oDate2-oDate1 (invert it via '-' for the more intuitive/common oDate1-oDate2)] SQL (MySQL): -timestampdiff(YEAR, MyDate1, MyDate2) [WARNING: timestampdiff() returns oDate2-oDate1 (invert it via '-' for the more intuitive/common oDate1-oDate2)] SQL (PostgreSQL): date_part('year', age(MyDate1, MyDate2)) SQL (SQLite): ___ [can use: substr(timediff(MyDate1, MyDate2), 1, 5)] [note: crops a friendly time diff string] Swift: UFL: DateDiffMonths [calculate the difference ignoring day/time, then reduce magnitude by 1 if necessary][count complete months (e.g. tiebreaks: Month1>Month2 but Day1<Day2, return vMonth1-vMonth2-1)][see also: DateFormatISOUTC/StrCompare/Sign] AutoHotkey: ___ [can use (assumes both dates are 6 digits or more, and of equal length): vDiffRaw := SubStr(vDate1, 1, 4)*12+SubStr(vDate1, 5, 2)-SubStr(vDate2, 1, 4)*12-SubStr(vDate2, 5, 2), vCmpRest := StrCompare(SubStr(vDate1, 7), SubStr(vDate2, 7)), vDiff := vDiffRaw + ((vDiffRaw>0)-(vDiffRaw<0) == -vCmpRest ? vCmpRest : 0)] [note: no sign function, so uses inequality workaround] C++: C#: Crystal: ___ [can use: vDiffRaw = oDate1.year*12+oDate1.month-oDate2.year*12-oDate2.month; vCmpRest = oDate1.to_s("%d%T%9N")<=>oDate2.to_s("%d%T%9N"); vDiff = vDiffRaw + ((vDiffRaw<=>0) == -vCmpRest ? vCmpRest : 0)] [note: no sign function, so uses 'vNum<=>0' workaround] Excel: ___ Excel VBA: ___ [can use: vDiffRaw = -DateDiff("m", oDate1, oDate2): vCmpRest = StrComp(Format(oDate1, "dd hh:mm:ss"), Format(oDate2, "dd hh:mm:ss")): vDiff = vDiffRaw + IIf(Sgn(vDiffRaw) = -vCmpRest, vCmpRest, 0)] Go: Java: vDiff = -java.time.temporal.ChronoUnit.MONTHS.between(oDate1, oDate2) [WARNING: between() returns oDate2-oDate1 (invert it via '-' for the more intuitive/common oDate1-oDate2)] JavaScript: ___ [can use: vDiffRaw = oDate1.getUTCFullYear()*12+oDate1.getUTCMonth()-oDate2.getUTCFullYear()*12-oDate2.getUTCMonth(); vCmpRest = +(oDate1.toISOString().slice(8)>oDate2.toISOString().slice(8))||-(oDate1.toISOString().slice(8)<oDate2.toISOString().slice(8)); vDiff = vDiffRaw + (Math.sign(vDiffRaw) == -vCmpRest ? vCmpRest : 0);] Kotlin: vDiff = -java.time.temporal.ChronoUnit.MONTHS.between(oDate1, oDate2) [WARNING: between() returns oDate2-oDate1 (invert it via '-' for the more intuitive/common oDate1-oDate2)] PHP: ___ [can use: $vDiff = -$oDate1->diff($oDate2)->format("%r%Y")*12-$oDate1->diff($oDate2)->format("%r%M")] [WARNING: $oDate1->diff($oDate2) returns $oDate2-$oDate1 (invert it via '-' for the more intuitive/common $oDate1-$oDate2)] Python: ___ [can use: vDiffRaw = oDate1.year*12+oDate1.month-oDate2.year*12-oDate2.month; vCmpRest = +(oDate1.strftime("%d%T%f")>oDate2.strftime("%d%T%f")) or -(oDate1.strftime("%d%T%f")<oDate2.strftime("%d%T%f")); vDiff = vDiffRaw + (vCmpRest if (math.copysign(1 if vDiffRaw else 0, vDiffRaw) == -vCmpRest) else 0)] [note: no sign function, so uses copysign workaround] [note: if dates not UTC, use '.astimezone(datetime.timezone.utc)'] R: Ruby: ___ [can use: vDiffRaw = oDate1.year*12+oDate1.month-oDate2.year*12-oDate2.month; vCmpRest = oDate1.strftime("%d%T%f")<=>oDate2.strftime("%d%T%f"); vDiff = vDiffRaw + ((vDiffRaw<=>0) == -vCmpRest ? vCmpRest : 0)] [note: no sign function, so uses 'vNum<=>0' workaround] Rust: Scala: vDiff = -java.time.temporal.ChronoUnit.MONTHS.between(oDate1, oDate2) [WARNING: between() returns oDate2-oDate1 (invert it via '-' for the more intuitive/common oDate1-oDate2)] SQL (MySQL): -timestampdiff(MONTH, MyDate1, MyDate2) [WARNING: timestampdiff() returns oDate2-oDate1 (invert it via '-' for the more intuitive/common oDate1-oDate2)] SQL (PostgreSQL): date_part('year', v)*12 + date_part('month', v) FROM age(MyDate1, MyDate2) v SQL (SQLite): ___ [can use: substr(timediff(MyDate1, MyDate2), 1, 8)] [note: crops a friendly time diff string, giving years and months] Swift: UFL: DateDiffDays [difference in days (count complete days: get difference and apply truncated division)] AutoHotkey: vDiff := DateDiff(vDate1, vDate2, "D") C++: C#: Crystal: vDiff = (oDate1-oDate2).total_days.to_i Excel: =TRUNC(A1-B1) [WARNING: gives incorrect values for Jan 1900 and Feb 1900 (e.g. 1900-03-01 minus 1900-02-28 is actually 1 day)] Excel VBA: vDiff = -DateDiff("d", oDate1, oDate2) [MAJOR WARNING: DateDiff returns oDate2-oDate1 (invert it via '-' for the more intuitive/common oDate1-oDate2)] [note: DateDiff gives correct values for Jan 1900 and Feb 1900] Go: Java: vDiff = -java.time.temporal.ChronoUnit.DAYS.between(oDate1, oDate2) [WARNING: between() returns oDate2-oDate1 (invert it via '-' for the more intuitive/common oDate1-oDate2)] JavaScript: vDiff = Math.trunc((oDate1.valueOf()-oDate2.valueOf())/86400000) Kotlin: vDiff = -java.time.temporal.ChronoUnit.DAYS.between(oDate1, oDate2) [WARNING: between() returns oDate2-oDate1 (invert it via '-' for the more intuitive/common oDate1-oDate2)] PHP: $vDiff = intdiv($oDate1->getTimestamp()-$oDate2->getTimestamp(), 86400) [also: $vDiff = -$oDate1->diff($oDate2)->format("%r%a")] [WARNING: $oDate1->diff($oDate2) returns $oDate2-$oDate1 (invert it via '-' for the more intuitive/common $oDate1-$oDate2)] Python: vDiff = int((oDate1-oDate2).total_seconds()/86400) R: Ruby: vDiff = ((oDate1-oDate2)/86400).to_i Rust: Scala: vDiff = -java.time.temporal.ChronoUnit.DAYS.between(oDate1, oDate2) [WARNING: between() returns oDate2-oDate1 (invert it via '-' for the more intuitive/common oDate1-oDate2)] SQL (MySQL): -timestampdiff(DAY, MyDate1, MyDate2) [WARNING: timestampdiff() returns oDate2-oDate1 (invert it via '-' for the more intuitive/common oDate1-oDate2)] SQL (PostgreSQL): (extract(epoch from age(MyDate1, MyDate2)) / 86400)::int SQL (SQLite): trunc(julianday(MyDate1)-julianday(MyDate2)) [also: trunc((unixepoch(MyDate1)-unixepoch(MyDate2))/86400)] Swift: UFL: DateDiffHours [difference in hours (count complete hours: get difference and apply truncated division)] AutoHotkey: vDiff := DateDiff(vDate1, vDate2, "H") C++: C#: Crystal: vDiff = (oDate1-oDate2).total_hours.to_i Excel: =TRUNC((A1-B1)*24) [WARNING: gives incorrect values for Jan 1900 and Feb 1900 (e.g. 1900-03-01 minus 1900-02-28 is actually 1 day)] Excel VBA: vDiff = -DateDiff("h", oDate1, oDate2) [MAJOR WARNING: DateDiff returns oDate2-oDate1 (invert it via '-' for the more intuitive/common oDate1-oDate2)] [note: DateDiff gives correct values for Jan 1900 and Feb 1900] Go: Java: vDiff = -java.time.temporal.ChronoUnit.HOURS.between(oDate1, oDate2) [WARNING: between() returns oDate2-oDate1 (invert it via '-' for the more intuitive/common oDate1-oDate2)] JavaScript: vDiff = Math.trunc((oDate1.valueOf()-oDate2.valueOf())/3600000) Kotlin: vDiff = -java.time.temporal.ChronoUnit.HOURS.between(oDate1, oDate2) [WARNING: between() returns oDate2-oDate1 (invert it via '-' for the more intuitive/common oDate1-oDate2)] PHP: $vDiff = intdiv($oDate1->getTimestamp()-$oDate2->getTimestamp(), 3600) Python: vDiff = int((oDate1-oDate2).total_seconds()/3600) R: Ruby: vDiff = ((oDate1-oDate2)/3600).to_i Rust: Scala: vDiff = -java.time.temporal.ChronoUnit.HOURS.between(oDate1, oDate2) [WARNING: between() returns oDate2-oDate1 (invert it via '-' for the more intuitive/common oDate1-oDate2)] SQL (MySQL): -timestampdiff(HOUR, MyDate1, MyDate2) [WARNING: timestampdiff() returns oDate2-oDate1 (invert it via '-' for the more intuitive/common oDate1-oDate2)] SQL (PostgreSQL): (extract(epoch from age(MyDate1, MyDate2)) / 3600)::int SQL (SQLite): trunc((julianday(MyDate1)-julianday(MyDate2))*24) [also: trunc((unixepoch(MyDate1)-unixepoch(MyDate2))/3600)] Swift: UFL: DateDiffMinutes [difference in minutes (count complete minutes: get difference and apply truncated division)] AutoHotkey: vDiff := DateDiff(vDate1, vDate2, "M") C++: C#: Crystal: vDiff = (oDate1-oDate2).total_minutes.to_i Excel: =TRUNC((A1-B1)*1440) [WARNING: gives incorrect values for Jan 1900 and Feb 1900 (e.g. 1900-03-01 minus 1900-02-28 is actually 1 day)] Excel VBA: vDiff = -DateDiff("n", oDate1, oDate2) [MAJOR WARNING: DateDiff returns oDate2-oDate1 (invert it via '-' for the more intuitive/common oDate1-oDate2)] [note: DateDiff gives correct values for Jan 1900 and Feb 1900] Go: Java: vDiff = -java.time.temporal.ChronoUnit.MINUTES.between(oDate1, oDate2) [WARNING: between() returns oDate2-oDate1 (invert it via '-' for the more intuitive/common oDate1-oDate2)] JavaScript: vDiff = Math.trunc((oDate1.valueOf()-oDate2.valueOf())/60000) Kotlin: vDiff = -java.time.temporal.ChronoUnit.MINUTES.between(oDate1, oDate2) [WARNING: between() returns oDate2-oDate1 (invert it via '-' for the more intuitive/common oDate1-oDate2)] PHP: $vDiff = intdiv($oDate1->getTimestamp()-$oDate2->getTimestamp(), 60) Python: vDiff = int((oDate1-oDate2).total_seconds()/60) R: Ruby: vDiff = ((oDate1-oDate2)/60).to_i Rust: Scala: vDiff = -java.time.temporal.ChronoUnit.MINUTES.between(oDate1, oDate2) [WARNING: between() returns oDate2-oDate1 (invert it via '-' for the more intuitive/common oDate1-oDate2)] SQL (MySQL): -timestampdiff(MINUTE, MyDate1, MyDate2) [WARNING: timestampdiff() returns oDate2-oDate1 (invert it via '-' for the more intuitive/common oDate1-oDate2)] SQL (PostgreSQL): (extract(epoch from age(MyDate1, MyDate2)) / 60)::int SQL (SQLite): trunc((julianday(MyDate1)-julianday(MyDate2))*1440) [also: trunc((unixepoch(MyDate1)-unixepoch(MyDate2))/60)] Swift: UFL: DateDiffSeconds [difference in seconds (count complete seconds: get difference and apply truncated division)] AutoHotkey: vDiff := DateDiff(vDate1, vDate2, "S") C++: C#: Crystal: vDiff = (oDate1-oDate2).total_seconds.to_i Excel: =TRUNC((A1-B1)*86400) [WARNING: gives incorrect values for Jan 1900 and Feb 1900 (e.g. 1900-03-01 minus 1900-02-28 is actually 1 day)] Excel VBA: vDiff = -DateDiff("s", oDate1, oDate2) [MAJOR WARNING: DateDiff returns oDate2-oDate1 (invert it via '-' for the more intuitive/common oDate1-oDate2)] [note: DateDiff gives correct values for Jan 1900 and Feb 1900] Go: Java: vDiff = -java.time.temporal.ChronoUnit.SECONDS.between(oDate1, oDate2) [WARNING: between() returns oDate2-oDate1 (invert it via '-' for the more intuitive/common oDate1-oDate2)] JavaScript: vDiff = Math.trunc((oDate1.valueOf()-oDate2.valueOf())/1000) Kotlin: vDiff = -java.time.temporal.ChronoUnit.SECONDS.between(oDate1, oDate2) [WARNING: between() returns oDate2-oDate1 (invert it via '-' for the more intuitive/common oDate1-oDate2)] PHP: $vDiff = $oDate1->getTimestamp()-$oDate2->getTimestamp() Python: vDiff = int((oDate1-oDate2).total_seconds()) [note: total_seconds() returns seconds and fractions of seconds] R: Ruby: vDiff = (oDate1-oDate2).to_i Rust: Scala: vDiff = -java.time.temporal.ChronoUnit.SECONDS.between(oDate1, oDate2) [WARNING: between() returns oDate2-oDate1 (invert it via '-' for the more intuitive/common oDate1-oDate2)] SQL (MySQL): -timestampdiff(SECOND, MyDate1, MyDate2) [WARNING: timestampdiff() returns oDate2-oDate1 (invert it via '-' for the more intuitive/common oDate1-oDate2)] SQL (PostgreSQL): (extract(epoch from age(MyDate1, MyDate2)))::int SQL (SQLite): trunc((julianday(MyDate1)-julianday(MyDate2))*86400) [also: trunc(unixepoch(MyDate1)-unixepoch(MyDate2))] Swift: UFL: DateDiffMilliseconds [difference in milliseconds (count complete milliseconds: get difference and apply truncated division)] AutoHotkey: ___ [can use (diff in whole seconds, times 1000): vDiff := DateDiff(vDate1, vDate2, "S") * 1000] C++: C#: Crystal: vDiff = (oDate1-oDate2).total_milliseconds.to_i Excel: =TRUNC((A1-B1)*86400000) [WARNING: gives incorrect values for Jan 1900 and Feb 1900 (e.g. 1900-03-01 minus 1900-02-28 is actually 1 day)] Excel VBA: vDiff = Fix((oDate1 - oDate2) * 86400000) Go: Java: vDiff = -java.time.temporal.ChronoUnit.MILLIS.between(oDate1, oDate2) [WARNING: between() returns oDate2-oDate1 (invert it via '-' for the more intuitive/common oDate1-oDate2)] JavaScript: vDiff = oDate1.valueOf() - oDate2.valueOf() Kotlin: vDiff = -java.time.temporal.ChronoUnit.MILLIS.between(oDate1, oDate2) [WARNING: between() returns oDate2-oDate1 (invert it via '-' for the more intuitive/common oDate1-oDate2)] PHP: $vDiff = ($oDate1->getTimestamp()-$oDate2->getTimestamp())*1000+$oDate1->format("v")-$oDate2->format("v") Python: vDiff = int((oDate1-oDate2).total_seconds() * 1000) [note: total_seconds() returns seconds and fractions of seconds] R: Ruby: vDiff = ((oDate1-oDate2)*1000).to_i Rust: Scala: vDiff = -java.time.temporal.ChronoUnit.MILLIS.between(oDate1, oDate2) [WARNING: between() returns oDate2-oDate1 (invert it via '-' for the more intuitive/common oDate1-oDate2)] SQL (MySQL): -timestampdiff(MICROSECOND, MyDate1, MyDate2) div 1000 [WARNING: timestampdiff() returns oDate2-oDate1 (invert it via '-' for the more intuitive/common oDate1-oDate2)] SQL (PostgreSQL): (extract(epoch from age(MyDate1, MyDate2))*1000)::int SQL (SQLite): trunc((julianday(MyDate1)-julianday(MyDate2))*86400000) [also: trunc((unixepoch(MyDate1, 'subsec')-unixepoch(MyDate2, 'subsec'))*1000)] [WARNING: without the 'subsec' modifier, unixepoch() returns an integer number of seconds] Swift: UFL: DateEaster [or DateGetEaster][get Gregorian (or Julian) Easter for a given year][if passed a date, get the year, get the Easter for that year] AutoHotkey: ___ C++: C#: Crystal: ___ Excel: ___ Excel VBA: ___ Go: Java: ___ JavaScript: ___ Kotlin: ___ PHP: $oDate = date_create_immutable("1970-01", timezone_open("UTC"))->modify(easter_date($vYear) . " secs") [also: easter_days()] [requires: 'have to compile PHP with --enable-calendar'] Python: ___ R: Ruby: ___ Rust: Scala: ___ SQL (MySQL): ___ SQL (PostgreSQL): ___ SQL (SQLite): ___ Swift: UFL: DateWorkdayNearest [or DateWeekdayNearest][nearest working day to (before/after) a specific date (if Sat, go back 1, if Sun, go forward 1) (i.e. choose the nearest M/T/W/T/F, a more general function lets you pick which weekdays)][WARNING: if the date is not UTC, this could skip by 2 days, e.g. the UK, on the day UTC starts, 1999-03-28 00:59:00 + 1 day] AutoHotkey: ___ C++: C#: Crystal: oDateNew = oDate + Time::Span.new(days:[0,0,0,0,0,-1,1][oDate.day_of_week.value-1]) [also: oDateNew = oDate + [0,0,0,0,0,-1,1][oDate.day_of_week.value-1].days] [note (day_of_week): 1-7, Mon = 1] Excel: =A1+MID("2111110",WEEKDAY(A1),1)-1 [note: WEEKDAY: 1-7, Sun = 1] Excel VBA: oDateNew = oDate + Mid("2111110", Weekday(oDate), 1) - 1 [note: Weekday: 1-7, Sun = 1] Go: Java: oDateNew = oDate.plusDays(new int[]{0,0,0,0,0,-1,1}[oDate.getDayOfWeek().getValue()-1]) [note: getDayOfWeek: 1-7, Mon = 1] JavaScript: oDateNew = new Date(Date.UTC(oDate.getFullYear(), oDate.getMonth(), oDate.getDate() + [1,0,0,0,0,0,-1][oDate.getDay()])) [note: getDay: 0-6, Sun = 0] Kotlin: oDateNew = oDate.plusDays(arrayOf<Long>(0,0,0,0,0,-1,1)[oDate.getDayOfWeek().getValue()-1]) [note: getDayOfWeek: 1-7, Mon = 1] PHP: $oDateNew = $oDate->modify([1,0,0,0,0,0,-1][$oDate->format("w")] . " days") [note (w): 0-6, Sun = 0] Python: oDateNew = oDate + datetime.timedelta(days=[0,0,0,0,0,-1,1][oDate.weekday()]) [note: weekday: 0-6, Mon = 0] [also: isoweekday: 1-7, Mon = 1] R: Ruby: oDateNew = oDate + [1,0,0,0,0,0,-1][oDate.wday]*86400 [note (wday): 0-6, Sun = 0] Rust: Scala: oDateNew = oDate.plusDays(Array(0,0,0,0,0,-1,1)(oDate.getDayOfWeek().getValue()-1)) [note: getDayOfWeek: 1-7, Mon = 1] SQL (MySQL): timestampadd(DAY, json_extract('[0,0,0,0,0,-1,1]', concat('$[', weekday(MyDate), ']')), MyDate) [note (weekday): 0-6, Mon = 0] SQL (PostgreSQL): date_add(MyDate, concat('[1,0,0,0,0,0,-1]'::json ->> date_part('dow', MyDate)::int, ' days')::interval) [note (dow): 0-6, Sun = 0] SQL (SQLite): datetime(MyDate, (substr('2111110', strftime('%w', MyDate)+1, 1) - 1)||' days') [note (w): 0-6, Sun = 0] Swift: UFL: DateWeekdayNextOrSame [or DateWeekdayNextOrCurrent][e.g. get the next Sunday, or today if today is Sunday][e.g. to get next Sunday excluding today, pass tomorrow as the date][see also: DateGetWeekday] AutoHotkey: vDateNew := DateAdd(vDate, Mod(7+vWDay-FormatTime(vDate, "WDay"), 7), "D") [note (WDay): 1-7, Sun = 1] C++: C#: Crystal: oDateNew = oDate + Time::Span.new(days:(vWDay-oDate.day_of_week.value) % 7) [note (day_of_week): 1-7, Mon = 1] [note: % is a floor mod, so x%7 will always be non-negative, so (7+x)%7 isn't needed] Excel: =A1+MOD(vWDay-WEEKDAY(A1),7) [note (WEEKDAY): 1-7, Sun = 1] [note: MOD() is a floor mod, so MOD(x,7) will always be non-negative, so MOD(7+x,7) isn't needed] Excel VBA: oDateNew = DateAdd("d", (7 + vWDay - Weekday(oDate)) Mod 7, oDate) [note (vbSunday): 1-7, Sun = 1 (but can use 2nd param to use other weekday systems, default: vbSunday)] Go: Java: ___ [e.g. this/next Sunday: oDateNew = oDate.with(java.time.temporal.TemporalAdjusters.nextOrSame(java.time.DayOfWeek.SUNDAY))] [note (DayOfWeek): 1-7, Mon = 1] JavaScript: oDateNew = new Date(oDate.valueOf() + ((7 + vWDay - oDate.getUTCDay()) % 7)*86400*1000) [note (getUTCDay/getDay): 0-6, Sun = 0] Kotlin: ___ [e.g. this/next Sunday: oDateNew = oDate.with(java.time.temporal.TemporalAdjusters.nextOrSame(java.time.DayOfWeek.SUNDAY))] [note (DayOfWeek): 1-7, Mon = 1] PHP: ___ [e.g. $oDateNew = $oDate->modify("this Sunday")] [also: weekday number to name, like 'w': $vWDayName = date("D", strtotime("Sunday +" . $vWDay . " days")); $oDateNew = $oDate->modify("this " . $vWDayName);] [note (w): 0-6, Sun = 0] Python: oDateNew = oDate + datetime.timedelta(days=(vWDay-oDate.weekday()) % 7) [note (weekday): 0-6, Mon = 0] [note: % is a floor mod, so x%7 will always be non-negative, so (7+x)%7 isn't needed] R: Ruby: oDateNew = oDate + ((vWDay-oDate.wday) % 7)*86400 [note (wday): 0-6, Sun = 0] [note: % is a floor mod, so x%7 will always be non-negative, so (7+x)%7 isn't needed] Rust: Scala: ___ [e.g. this/next Sunday: oDateNew = oDate.`with`(java.time.temporal.TemporalAdjusters.nextOrSame(java.time.DayOfWeek.SUNDAY))] [note (DayOfWeek): 1-7, Mon = 1] SQL (MySQL): timestampadd(DAY, (7+MyWDay-weekday(MyDate)) % 7, MyDate) [note (weekday): 0-6, Mon = 0] [note (dayofweek): 1-7, Sun = 1 (ODBC standard)] SQL (PostgreSQL): date_add(MyDate, concat((7+MyWDay-date_part('dow', MyDate)::int) % 7, ' days')::interval) [note (dow): 0-6, Sun = 0] SQL (SQLite): ___ [e.g. this/next Sunday: datetime(MyDate, 'weekday 0')] [note (weekday): 0-6, Sun = 0] Swift: UFL: DateWeekdayNthInRange [nth weekday in range (e.g. 1st/2nd/3rd/4th/last Sunday of the month)][see also: DateWeekdayNextOrCurrent] AutoHotkey: ___ C++: C#: Crystal: ___ Excel: ___ Excel VBA: ___ Go: Java: ___ JavaScript: ___ Kotlin: ___ PHP: ___ [can use: e.g. 6th Monday of the year: $oDateNew = $oDate->modify("first day of January")->modify("sixth Monday");] Python: ___ R: Ruby: ___ Rust: Scala: ___ SQL (MySQL): ___ SQL (PostgreSQL): ___ SQL (SQLite): ___ [e.g. 3rd Sunday on/after date: datetime(oDate, 'weekday 0', '14 days')] [note: 'weekday 0' modifier: first Sunday on/after date] [note (weekday): 0-6, Sun = 0] Swift: UFL: DateWeekdayNthInMonth [nth weekday in month (e.g. 1st/2nd/3rd/4th Sunday of the month)][see also: DateWeekdayNextOrCurrent] AutoHotkey: ___ C++: C#: Crystal: ___ Excel: ___ Excel VBA: ___ Go: Java: oDate = java.time.ZonedDateTime.now().truncatedTo(java.time.temporal.ChronoUnit.DAYS).withMonth(vMonth).withYear(vYear).with(java.time.temporal.TemporalAdjusters.dayOfWeekInMonth(vNum, java.time.DayOfWeek.SUNDAY)) [e.g. vNum 1 for 1st Sunday in month] [WARNING: vNum 5 and above may return days in later months] [note: for the last Sunday of the month, use vNum 0, and specify the subsequent month (i.e. 1 week before the first Sunday of next month)] JavaScript: ___ Kotlin: oDate = java.time.ZonedDateTime.now().truncatedTo(java.time.temporal.ChronoUnit.DAYS).withMonth(vMonth).withYear(vYear).with(java.time.temporal.TemporalAdjusters.dayOfWeekInMonth(vNum, java.time.DayOfWeek.SUNDAY)) [e.g. vNum 1 for 1st Sunday in month] [WARNING: vNum 5 and above may return days in later months] [note: for the last Sunday of the month, use vNum 0, and specify the subsequent month (i.e. 1 week before the first Sunday of next month)] PHP: ___ [e.g. $oDateNew = $oDate->modify("third Sunday of this month")] Python: ___ R: Ruby: ___ Rust: Scala: oDate = java.time.ZonedDateTime.now().truncatedTo(java.time.temporal.ChronoUnit.DAYS).withMonth(vMonth).withYear(vYear).`with`(java.time.temporal.TemporalAdjusters.dayOfWeekInMonth(vNum, java.time.DayOfWeek.SUNDAY)) [e.g. vNum 1 for 1st Sunday in month] [WARNING: vNum 5 and above may return days in later months] [note: for the last Sunday of the month, use vNum 0, and specify the subsequent month (i.e. 1 week before the first Sunday of next month)] SQL (MySQL): ___ SQL (PostgreSQL): ___ SQL (SQLite): ___ [e.g. 3rd Sunday of the month: datetime(oDate, 'start of month', 'weekday 0', '14 days')] [note: 'weekday 0' modifier: first Sunday on/after date] [note (weekday): 0-6, Sun = 0] Swift: UFL: DateWeekdayLastInMonth [last weekday in month (e.g. last Sunday of the month)][workaround: get first Sunday of next month, then subtract 7 days][see also: DateWeekdayNextOrCurrent] AutoHotkey: ___ C++: C#: Crystal: ___ Excel: ___ Excel VBA: ___ Go: Java: oDate = java.time.YearMonth.of(vYear, vMonth).atEndOfMonth().with(java.time.temporal.TemporalAdjusters.previousOrSame(java.time.DayOfWeek.SUNDAY)).atStartOfDay(java.time.ZoneOffset.UTC) [note: atEndOfMonth() returns a LocalDate] JavaScript: ___ Kotlin: oDate = java.time.YearMonth.of(vYear, vMonth).atEndOfMonth().with(java.time.temporal.TemporalAdjusters.previousOrSame(java.time.DayOfWeek.SUNDAY)).atStartOfDay(java.time.ZoneOffset.UTC) [note: atEndOfMonth() returns a LocalDate] PHP: ___ [e.g. $oDateNew = $oDate->modify("last Sunday of this month")] Python: ___ R: Ruby: ___ Rust: Scala: oDate = java.time.YearMonth.of(vYear, vMonth).atEndOfMonth().`with`(java.time.temporal.TemporalAdjusters.previousOrSame(java.time.DayOfWeek.SUNDAY)).atStartOfDay(java.time.ZoneOffset.UTC) [note: atEndOfMonth() returns a LocalDate] SQL (MySQL): ___ [can use: last_day(MyDate) for the last day of the month, then subtract 6 days, then the 'DateWeekdayNextOrCurrent' approach] SQL (PostgreSQL): ___ SQL (SQLite): ___ [e.g. last Sunday of the month: datetime(oDate, 'start of month', '1 month', '-7 days', 'weekday 0')] [note: 'weekday 0' modifier: first Sunday on/after date] [note (weekday): 0-6, Sun = 0] Swift: UFL: DateGetPart [or DateGetValue][e.g. get Y/M/D/H/M/S][see also: DateSplit/DateFormatISOUTC/DateFormat14] AutoHotkey: ___ [can use: vPart := FormatTime(vDate, vFormat)] C++: C#: Crystal: ___ [can use: vPart = oDate.to_s(vFormat)] Excel: ___ [can use: TEXT(A1,vFormat)] Excel VBA: ___ [can use: vPart = Format(oDate, vFormat)] Go: Java: vPart = oDate.getLong(oUnit) JavaScript: ___ Kotlin: vPart = oDate.getLong(oUnit) PHP: ___ [can use: $vPart = $oDate->format($vFormat)] Python: ___ [can use: vPart = oDate.strftime(vFormat)] R: Ruby: ___ [can use: vPart = oDate.strftime(vFormat)] Rust: Scala: vPart = oDate.getLong(oUnit) SQL (MySQL): extract(MyUnit from MyDate) [e.g. extract(YEAR from MyDate)] [also: date(), time()] SQL (PostgreSQL): date_part(MyUnit, MyDate) [e.g. day: date_part('day', now())] [also (e.g. day): extract(day from now())] SQL (SQLite): ___ [can use: strftime(MyFormat, MyDate)] Swift: UFL: DateGetYear [date get year (e.g. a 4-digit number)] AutoHotkey: vYear := FormatTime(vDate, "yyyy") C++: C#: Crystal: vYear = oDate.year [also (zero-padded): oDate.to_s("%Y")] Excel: =YEAR(A1) [also: =TEXT(A1,"yyyy")] Excel VBA: vYear = Year(oDate) [also: Format(oDate, "yyyy")] Go: Java: vYear = oDate.getYear() [also: oDate.format(java.time.format.DateTimeFormatter.ofPattern("yyyy"))] JavaScript: vYear = oDate.getUTCFullYear() [also: oDate.getFullYear()] Kotlin: vYear = oDate.getYear() [also: oDate.format(java.time.format.DateTimeFormatter.ofPattern("yyyy"))] PHP: $vYear = (int)$oDate->format("Y") [also (zero-padded): $oDate->format("Y")] Python: vYear = oDate.year [also: oDate.strftime("%Y")] R: Ruby: vYear = oDate.year [also: oDate.strftime("%Y")] Rust: Scala: vYear = oDate.getYear() [also: oDate.format(java.time.format.DateTimeFormatter.ofPattern("yyyy"))] SQL (MySQL): year(MyDate) [also: date_format(MyDate, '%Y')] SQL (PostgreSQL): date_part('year', MyDate) [also: to_char(MyDate, 'YYYY')] SQL (SQLite): strftime('%Y', MyDate) Swift: UFL: DateGetMonth [date get month (1-12) (some systems use 0-11)] AutoHotkey: vMonth := FormatTime(vDate, "M") [also (zero-padded): 'MM'] C++: C#: Crystal: vMonth = oDate.month [also (zero-padded): oDate.to_s("%m")] Excel: =MONTH(A1) [also (zero-padded): =TEXT(A1,"mm")] [WARNING: 'mm' returns months/minutes depending on surrounding chars] Excel VBA: vMonth = Month(oDate) [also (zero-padded): Format(oDate, "mm")] [WARNING: 'mm' returns months/minutes depending on surrounding chars] Go: Java: vMonth = oDate.getMonthValue() [also: getMonth()] [also (zero-padded): oDate.format(java.time.format.DateTimeFormatter.ofPattern("MM"))] JavaScript: vMonth = oDate.getUTCMonth() + 1 [also: oDate.getMonth() + 1] [WARNING: getUTCMonth/getMonth are 0-based] Kotlin: vMonth = oDate.getMonthValue() [also: getMonth()] [also (zero-padded): oDate.format(java.time.format.DateTimeFormatter.ofPattern("MM"))] PHP: $vMonth = (int)$oDate->format("m") [also (zero-padded): $oDate->format("m")] Python: vMonth = oDate.month [also (zero-padded): oDate.strftime("%m")] R: Ruby: vMonth = oDate.month [alias: oDate.mon] [also (zero-padded): oDate.strftime("%m")] Rust: Scala: vMonth = oDate.getMonthValue() [also: getMonth()] [also (zero-padded): oDate.format(java.time.format.DateTimeFormatter.ofPattern("MM"))] SQL (MySQL): month(MyDate) [also (zero-padded): date_format(MyDate, '%m')] SQL (PostgreSQL): date_part('month', MyDate) [also (zero-padded): to_char(MyDate, 'MM')] SQL (SQLite): strftime('%m', MyDate) Swift: UFL: DateGetDay [date get day of the month (1-31)][get the 'month day'] AutoHotkey: vDay := FormatTime(vDate, "d") [also (zero-padded): 'dd'] C++: C#: Crystal: vDay = oDate.day [also (zero-padded): oDate.to_s("%d")] Excel: =DAY(A1) [also (zero-padded): =TEXT(A1,"dd")] Excel VBA: vDay = Day(oDate) [also (zero-padded): Format(oDate, "dd")] Go: Java: vDay = oDate.getDayOfMonth() [also (zero-padded): oDate.format(java.time.format.DateTimeFormatter.ofPattern("dd"))] JavaScript: vDay = oDate.getUTCDate() [also: oDate.getDate()] [WARNING: 'Date' not 'Day'] Kotlin: vDay = oDate.getDayOfMonth() [also (zero-padded): oDate.format(java.time.format.DateTimeFormatter.ofPattern("dd"))] PHP: $vDay = (int)$oDate->format("d") [also (zero-padded): $oDate->format("d")] Python: vDay = oDate.day [also (zero-padded): oDate.strftime("%d")] R: Ruby: vDay = oDate.mday [alias: oDate.day] [also (zero-padded): oDate.strftime("%d")] Rust: Scala: vDay = oDate.getDayOfMonth() [also (zero-padded): oDate.format(java.time.format.DateTimeFormatter.ofPattern("dd"))] SQL (MySQL): day(MyDate) [alias (dayofmonth): day] [also (zero-padded): date_format(MyDate, '%d')] SQL (PostgreSQL): date_part('day', MyDate) [also (zero-padded): to_char(MyDate, 'DD')] SQL (SQLite): strftime('%d', MyDate) Swift: UFL: DateGetHours [date get hour (0-23)] AutoHotkey: vHour := FormatTime(vDate, "H") [also (zero-padded): 'HH'] C++: C#: Crystal: vHour = oDate.hour [also (zero-padded): oDate.to_s("%H")] Excel: =HOUR(A1) [also (zero-padded): =TEXT(A1,"hh")] Excel VBA: vHour = Hour(oDate) [also (zero-padded): Format(oDate, "hh")] Go: Java: vHour = oDate.getHour() [also (zero-padded): oDate.format(java.time.format.DateTimeFormatter.ofPattern("HH"))] JavaScript: vHour = oDate.getUTCHours() [also: oDate.getHours()] Kotlin: vHour = oDate.getHour() [also (zero-padded): oDate.format(java.time.format.DateTimeFormatter.ofPattern("HH"))] PHP: $vHour = (int)$oDate->format("H") [also (zero-padded): $oDate->format("H")] Python: vHour = oDate.hour [also (zero-padded): oDate.strftime("%H")] R: Ruby: vHour = oDate.hour [also (zero-padded): oDate.strftime("%H")] Rust: Scala: vHour = oDate.getHour() [also (zero-padded): oDate.format(java.time.format.DateTimeFormatter.ofPattern("HH"))] SQL (MySQL): hour(MyDate) [also (zero-padded): date_format(MyDate, '%H')] SQL (PostgreSQL): date_part('hour', MyDate) [also (zero-padded): to_char(MyDate, 'HH24')] SQL (SQLite): strftime('%H', MyDate) Swift: UFL: DateGetMinutes [date get minute (0-59)] AutoHotkey: vMin := FormatTime(vDate, "m") [also (zero-padded): 'mm'] C++: C#: Crystal: vMin = oDate.minute [also (zero-padded): oDate.to_s("%M")] Excel: =MINUTE(A1) [WARNING: TEXT(): 'mm' returns months/minutes depending on surrounding chars] Excel VBA: vMin = Minute(oDate) [also (zero-padded): Format(oDate, "nn")] [WARNING: 'mm' returns months/minutes depending on surrounding chars] Go: Java: vMin = oDate.getMinute() [also (zero-padded): oDate.format(java.time.format.DateTimeFormatter.ofPattern("mm"))] JavaScript: vMin = oDate.getUTCMinutes() [also: oDate.getMinutes()] Kotlin: vMin = oDate.getMinute() [also (zero-padded): oDate.format(java.time.format.DateTimeFormatter.ofPattern("mm"))] PHP: $vMin = (int)$oDate->format("i") [also (zero-padded): $oDate->format("i")] Python: vMin = oDate.minute [also (zero-padded): oDate.strftime("%M")] R: Ruby: vMin = oDate.min [also (zero-padded): oDate.strftime("%M")] Rust: Scala: vMin = oDate.getMinute() [also (zero-padded): oDate.format(java.time.format.DateTimeFormatter.ofPattern("mm"))] SQL (MySQL): minute(MyDate) [also (zero-padded): date_format(MyDate, '%i')] SQL (PostgreSQL): date_part('minute', MyDate) [also (zero-padded): to_char(MyDate, 'MI')] SQL (SQLite): strftime('%M', MyDate) Swift: UFL: DateGetSeconds [date get second (0-59)] AutoHotkey: vSec := FormatTime(vDate, "s") [also (zero-padded): 'ss'] C++: C#: Crystal: vSec = oDate.second [also (zero-padded): oDate.to_s("%S")] Excel: =SECOND(A1) [also (zero-padded): =TEXT(A1,"ss")] Excel VBA: vSec = Second(oDate) [also (zero-padded): Format(oDate, "ss")] Go: Java: vSec = oDate.getSecond() [also (zero-padded): oDate.format(java.time.format.DateTimeFormatter.ofPattern("ss"))] JavaScript: vSec = oDate.getUTCSeconds() [also: oDate.getSeconds()] Kotlin: vSec = oDate.getSecond() [also (zero-padded): oDate.format(java.time.format.DateTimeFormatter.ofPattern("ss"))] PHP: $vSec = (int)$oDate->format("s") [also (zero-padded): $oDate->format("s")] Python: vSec = oDate.second [also (zero-padded): oDate.strftime("%S")] R: Ruby: vSec = oDate.sec [also (zero-padded): oDate.strftime("%S")] Rust: Scala: vSec = oDate.getSecond() [also (zero-padded): oDate.format(java.time.format.DateTimeFormatter.ofPattern("ss"))] SQL (MySQL): second(MyDate) [also (zero-padded): date_format(MyDate, '%S')] SQL (PostgreSQL): date_part('second', MyDate) [also (zero-padded): to_char(MyDate, 'SS')] SQL (SQLite): strftime('%S', MyDate) Swift: UFL: DateGetMilliseconds [or DateGetMSec][date get milliseconds (0-999)] AutoHotkey: ___ C++: C#: Crystal: vMSec = oDate.millisecond [also (zero-padded): oDate.to_s("%L")] [also (zero-padded): oDate.to_s("%3N")] Excel: =RIGHT(TEXT(A1,"s.000"),3) [also: MOD(ROUND((A1-INT(A1))*86400000,0),1000)] Excel VBA: vMSec = Round((oDate - Int(oDate)) * 86400000) Mod 1000 Go: Java: vMSec = oDate.getNano() / 1000000 [note: truncated division] JavaScript: vMSec = oDate.getUTCMilliseconds() [also: oDate.getMilliseconds()] Kotlin: vMSec = oDate.getNano() / 1000000 [note: truncated division] PHP: $vMSec = (int)$oDate->format("v") [also (zero-padded): $oDate->format("v")] Python: vMSec = oDate.microsecond // 1000 R: Ruby: vMSec = oDate.usec / 1000 [note: floor division] [also (zero-padded): oDate.strftime("%L")] Rust: Scala: vMSec = oDate.getNano() / 1000000 [note: truncated division] SQL (MySQL): microsecond(MyDate) div 1000 [note: truncated division] SQL (PostgreSQL): date_part('millisecond', MyDate)::int % 1000 SQL (SQLite): substr(strftime('%f', MyDate), 4) [note ('%f', seconds/milliseconds): '##.###'] Swift: UFL: DateGetWeekday [or DateGetWDay/DateGetWeekdayIndex/DateGetWeekdayNumber][typically 0-6 or 1-7] AutoHotkey: vWDay := FormatTime(vDate, "WDay") [note (WDay): 1-7, Sun = 1] C++: C#: Crystal: vWDay = oDate.day_of_week.value [also: oDate.to_s("%w")] [note (w): 0-6, Sun = 0] [also: oDate.to_s("%u")] [note (day_of_week/u): 1-7, Mon = 1] Excel: =WEEKDAY(A1) [note (WEEKDAY): 1-7, Sun = 1 (but can use 2nd param to use other weekday systems)] [WARNING: WEEKDAY gives incorrect values for Jan 1900 and Feb 1900 (e.g. 1900-01-01 was actually a Monday)] Excel VBA: vWDay = Weekday(oDate) [note (vbSunday): 1-7, Sun = 1 (but can use 2nd param to use other weekday systems, default: vbSunday)] [note: Weekday and WorksheetFunction.Weekday give correct values for Jan 1900 and Feb 1900] [MAJOR WARNING: for Weekday(), vbSunday is the default, for WeekdayName(), vbUseSystemDayOfWeek (aka vbUseSystem) is the default] Go: Java: vWDay = oDate.getDayOfWeek().getValue() [note (DayOfWeek): 1-7, Mon = 1] [note: int to DayOfWeek: java.time.DayOfWeek.of(vWDay)] JavaScript: vWDay = oDate.getUTCDay() [also: oDate.getDay()] [WARNING: 'Day' not 'WDay'/'Weekday'] [note (getUTCDay/getDay): 0-6, Sun = 0] Kotlin: vWDay = oDate.getDayOfWeek().getValue() [note (DayOfWeek): 1-7, Mon = 1] [note: int to DayOfWeek: java.time.DayOfWeek.of(vWDay)] PHP: $vWDay = (int)$oDate->format("N") [also: (int)$oDate->format("w")] [note (N): 1-7, Mon = 1] [note (w): 0-6, Sun = 0] Python: vWDay = oDate.isoweekday() [note (isoweekday): 1-7, Mon = 1] [also: oDate.weekday()] [note (weekday): 0-6, Mon = 0] R: Ruby: vWDay = oDate.wday [also: oDate.strftime("%w")] [note (wday/w): 0-6, Sun = 0] [also: oDate.strftime("%u")] [note (u): 1-7, Mon = 1] Rust: Scala: vWDay = oDate.getDayOfWeek().getValue() [note (DayOfWeek): 1-7, Mon = 1] [note: int to DayOfWeek: java.time.DayOfWeek.of(vWDay)] SQL (MySQL): weekday(MyDate) [also: dayofweek(MyDate)] [date_format(MyDate, '%w')] [note (weekday): 0-6, Mon = 0] [note (dayofweek): 1-7, Sun = 1 (ODBC standard)] [note (w): 0-6, Sun = 0] SQL (PostgreSQL): date_part('dow', MyDate) [also: date_part('isodow', MyDate)] [also: to_char(MyDate, 'D')] [also: to_char(MyDate, 'ID')] [note (dow): 0-6, Sun = 0] [note: (isodow/ID): 1-7, Mon = 1] [note: (D): 1-7, Sun = 1] SQL (SQLite): strftime('%w', MyDate) [note (w): 0-6, Sun = 0] [also: strftime('%u', MyDate)] [note (u): 1-7, Mon = 1] Swift: UFL: DateGetWeek [get week number, *not* ISO week numbers, simple system][get week number (1-54) (week containing January 1st is numbered week 1, weeks start on Monday)][e.g. if 1st Jan is Sun, week 1 contains 1 day only][algorithm: week = truncdiv(yearday(date)-1+x, 7) + 1 = truncdiv(yearday(date)+6+x, 7) (note: if year starts on Mon: x=0, on Tue: x=1 etc, use the weekday index, of the first day of the year, mod 7, to calculate x) (note: where yearday() returns 1-366)] AutoHotkey: ___ [can use: vWeek := (FormatTime(vDate, "YDay") + 6 + Mod(FormatTime(SubStr(vDate, 1, 4), "WDay")+5, 7)) // 7] [note (WDay): 1-7, Sun = 1] [note: uses the year day, and the weekday index for the first day of the year] C++: C#: Crystal: ___ [can use: vWeek = (oDate.day_of_year + 6 + (oDate.at_beginning_of_year.day_of_week.value+6)%7) // 7] [note: truncated division] [note (day_of_week): 1-7, Mon = 1] Excel: =WEEKNUM(A1,2) Excel VBA: vWeek = Format(oDate, "ww", vbMonday) Go: Java: vWeek = oDate.get(java.time.temporal.WeekFields.of(java.time.DayOfWeek.MONDAY, 1).weekOfYear()) JavaScript: ___ [can use: oDateYearStart = new Date(Date.UTC(oDate.getUTCFullYear(), 0, 1)); vWeek = Math.floor((Math.floor((oDate-oDateYearStart)/86400000) + 7 + ((oDateYearStart.getUTCDay()+6) % 7)) / 7);] [note (getUTCDay): 0-6, Sun = 0] [note: since the numbers are non-negative, both Math.floor and Math.trunc can be used] [note: uses the year day, and the weekday index for the first day of the year] Kotlin: vWeek = oDate.get(java.time.temporal.WeekFields.of(java.time.DayOfWeek.MONDAY, 1).weekOfYear()) PHP: ___ [can use: $vWeek = intdiv((int)$oDate->format("z") + 7 + (((int)$oDate->modify("first day of January")->format("N")+6)%7), 7)] [note: truncated division] [note (N): 1-7, Mon = 1] [note (w): 0-6, Sun = 0] [WARNING: 'z' returns a 0-based year day (0-364 in common years, 0-365 in leap years), most programming languages return a 1-based year day] [MAJOR WARNING: 'first day of this year' actually returns 'first day of this month'] Python: vWeek = int(oDate.strftime("%W")) + (1 if datetime.date(oDate.year, 1, 1).weekday() else 0) [note (weekday): 0-6, Mon = 0] [note (%W): week 1 is first week that contains Monday, thus 1st Jan is week 1 if it's a Monday, else week 0] R: Ruby: vWeek = oDate.strftime("%W").to_i + (Time.new(oDate.year, 1, 1).wday != 1 ? 1 : 0) [note (wday): 0-6, Sun = 0] [note (%W): week 1 is first week that contains Monday, thus 1st Jan is week 1 if it's a Monday, else week 0] Rust: Scala: vWeek = oDate.get(java.time.temporal.WeekFields.of(java.time.DayOfWeek.MONDAY, 1).weekOfYear()) SQL (MySQL): ___ [can use: (dayofyear(MyDate) + 6 + mod(dayofweek(makedate(MyDate,1))+5, 7)) div 7] [note: truncated division] [note (dayofweek): 1-7, Sun = 1 (ODBC standard)] [note: uses the year day, and the weekday index for the first day of the year] [note: makedate() accepts 'year,day of year' but also 'date,day of year'] SQL (PostgreSQL): ___ [can use: (date_part('doy', MyDate)::int + 6 + mod(date_part('dow', date_trunc('year', MyDate))::int+6, 7)) / 7] [note: truncated division] [note (dow): 0-6, Sun = 0] [note: uses the year day, and the weekday index for the first day of the year] [WARNING: to_char(MyDate, 'WW') gives a (peculiar?) week number where the first 7 days of the year are week 1 (the weekday indexes are ignored)] SQL (SQLite): strftime('%W', MyDate) + (strftime('%w', MyDate, 'start of year') != '1') [note (w): 0-6, Sun = 0] [note (%W): week 1 is first week that contains Monday, thus 1st Jan is week 1 if it's a Monday, else week 0] Swift: UFL: DateGetISOYearWeek [get ISO-8601 week number (and year) (e.g. 2006-05-04 is in week '200618') (ISO week: 1-53) (week containing the first Thursday of the year is numbered week 1, weeks start on Monday)][WARNING: the first ISO week of year n may have year n-1][WARNING: the last ISO week of year n may have year n+1] AutoHotkey: vIsoYWeek := FormatTime(vDate, "YWeek") [note: number of the form 'yyyyww'] C++: C#: Crystal: vIsoYWeek = oDate.to_s("%G%V") Excel: ___ [can use (without year): ISOWEEKNUM(A1)] [also (without year): WEEKNUM(A1,21)] [note: ISOWEEKNUM added in Excel 2013, WEEKNUM '21' option added in Excel 2010] [also (year): YEAR(A1-WEEKDAY(A1-1)+4)] [can use (without year): =INT((A1-DATE(YEAR(A1-WEEKDAY(A1-1)+4),1,3)+WEEKDAY(DATE(YEAR(A1-WEEKDAY(A1-1)+4),1,3))+5)/7)] Excel VBA: ___ [can use (without year): vIsoWeek = WorksheetFunction.IsoWeekNum(oDate)] [WARNING: returns the ISO week number 1-53, but not the ISO year] [WARNING: Format/DatePart with 'ww'/vbMonday/vbFirstFourDays sometimes return the wrong week: 'the last Monday in some calendar years is returned as week 53 when it should be week 1'] [also (year): vIsoYear = Year(CDbl(oDate) - Weekday(oDate, 2) + 4)] Go: Java: vIsoYWeek = oDate.get(java.time.temporal.IsoFields.WEEK_BASED_YEAR)*100 + oDate.get(java.time.temporal.IsoFields.WEEK_OF_WEEK_BASED_YEAR) JavaScript: ___ Kotlin: vIsoYWeek = oDate.get(java.time.temporal.IsoFields.WEEK_BASED_YEAR)*100 + oDate.get(java.time.temporal.IsoFields.WEEK_OF_WEEK_BASED_YEAR) PHP: $vIsoYWeek = $oDate->format("oW") Python: vIsoYWeek = oDate.strftime("%G%V") [also: oDate.isocalendar(): contains year/week(/weekday) values] R: Ruby: vIsoYWeek = oDate.strftime("%G%V") Rust: Scala: vIsoYWeek = oDate.get(java.time.temporal.IsoFields.WEEK_BASED_YEAR)*100 + oDate.get(java.time.temporal.IsoFields.WEEK_OF_WEEK_BASED_YEAR) SQL (MySQL): yearweek(MyDate, 3) [also (week with year): date_format(MyDate, '%x%v')] [also (week without year): weekofyear(MyDate)] [also (week without year): week(MyDate, 3)] SQL (PostgreSQL): date_part('isoyear', now())*100 + date_part('week', now()) [also: to_char(now(), 'IYYYIW')] SQL (SQLite): strftime('%G%V', MyDate) Swift: UFL: DateGetMonthName [e.g. the long/short forms of Jan/Feb/Mar/Apr/May/Jun/Jul/Aug/Sep/Oct/Nov/Dec][prefer the short version] AutoHotkey: vMonthName := FormatTime(vDate, "MMM") [also (long form): 'MMMM'] C++: C#: Crystal: vMonthName = oDate.to_s("%b") [also (long form): '%B'] Excel: =TEXT(A1,"mmm") [also (long form): 'mmmm'] Excel VBA: vMonthName = Format(oDate, "mmm") [also (long form): 'mmmm'] [also (short form): MonthName(DatePart("m", oDate), True)] [also (long form): MonthName(DatePart("m", oDate))] Go: Java: vMonthName = oDate.getMonth().toString() [note (getMonth): long form, upper case] JavaScript: vMonthName = oDate.toLocaleString("en", {month:"short"}) [also (long form): 'long'] Kotlin: vMonthName = oDate.getMonth().toString() [note (getMonth): long form, upper case] PHP: $vMonthName = $oDate->format("M") [also (long form): 'F'] Python: vMonthName = oDate.strftime("%b") [also (long form): '%B'] R: Ruby: vMonthName = oDate.strftime("%b") [also (long form): '%B'] Rust: Scala: vMonthName = oDate.getMonth().toString() [note (getMonth): long form, upper case] SQL (MySQL): date_format(MyDate, '%b') [note (%b): short form] [also (long form): monthname(MyDate)] [also (long form): date_format(MyDate, '%M')] SQL (PostgreSQL): to_char(MyDate, 'Mon') [note (Mon): short form] [also (long form): to_char(MyDate, 'Month')] SQL (SQLite): '["Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"]' ->> (strftime('%m', MyDate)-1) Swift: UFL: DateGetWeekdayName [or DateGetWDayName][e.g. the long/short forms of Mon/Tue/Wed/Thu/Fri/Sat/Sun][prefer the short version] AutoHotkey: vWDayName := FormatTime(vDate, "ddd") [also (long form): 'dddd'] C++: C#: Crystal: vWDayName = oDate.to_s("%a") [also (long form): '%A'] Excel: =TEXT(A1,"ddd") [also (long form): 'dddd'] Excel VBA: vWDayName = Format(oDate, "ddd") [also (long form): 'dddd'] [also (short form): WeekdayName(Weekday(oDate), True, vbSunday)] [also (long form): WeekdayName(Weekday(oDate), , vbSunday)] [note (Weekday/WeekdayName with vbSunday): 1-7, Sun = 1] [note (WeekdayName with vbMonday): 1-7, Mon = 1] [MAJOR WARNING: for WeekdayName(), vbUseSystemDayOfWeek (aka vbUseSystem) is the default, for Weekday(), vbSunday is the default] Go: Java: vWDayName = oDate.getDayOfWeek().toString() [note (getDayOfWeek): long form, upper case] JavaScript: vWDayName = oDate.toLocaleString("en", {weekday:"short"}) [also (long form): 'long'] Kotlin: vWDayName = oDate.getDayOfWeek().toString() [note (getDayOfWeek): long form, upper case] PHP: $vWDayName = $oDate->format("D") [also (long form): 'l' (note: small 'L')] Python: vWDayName = oDate.strftime("%a") [also (long form): '%A'] R: Ruby: vWDayName = oDate.strftime("%a") [also (long form): '%A'] Rust: Scala: vWDayName = oDate.getDayOfWeek().toString() [note (getDayOfWeek): long form, upper case] SQL (MySQL): date_format(MyDate, '%a') [note (%a): short form] [also (long form): dayname(MyDate)] [also (long form): date_format(MyDate, '%W')] SQL (PostgreSQL): to_char(MyDate, 'Dy') [note (Dy): short form] [also (long form): to_char(MyDate, 'Day')] SQL (SQLite): '["Sun","Mon","Tue","Wed","Thu","Fri","Sat"]' ->> strftime('%w', MyDate) Swift: UFL: DateGetHour12 [or DateGetHourAmPm][i.e. 1-12] AutoHotkey: vHour12 := FormatTime(vDate, "h") [also (zero-padded): 'hh'] C++: C#: Crystal: vHour12 = oDate.to_s("%l") [note (%l): space-padded] [also (zero-padded): '%I'] Excel: =MOD(HOUR(A1)+11,12)+1 Excel VBA: vHour12 = ((Hour(oDate) + 11) Mod 12) + 1 Go: Java: vHour12 = oDate.format(java.time.format.DateTimeFormatter.ofPattern("h")) [also (zero-padded): 'hh'] JavaScript: vHour12 = oDate.getUTCHours()%12||12 [also: oDate.getHours()%12||12] Kotlin: vHour12 = oDate.format(java.time.format.DateTimeFormatter.ofPattern("h")) [also (zero-padded): 'hh'] PHP: $vHour12 = (int)$oDate->format("g") [also (zero-padded): 'h'] Python: vHour12 = oDate.strftime("%l") [note (%l): space-padded] [also (zero-padded): '%I'] R: Ruby: vHour12 = oDate.strftime("%l") [note (%l): space-padded] [also (zero-padded): '%I'] Rust: Scala: vHour12 = oDate.format(java.time.format.DateTimeFormatter.ofPattern("h")) [also (zero-padded): 'hh'] SQL (MySQL): date_format(MyDate, '%l') [note (%l): space-padded] [also (zero-padded): '%h'] SQL (PostgreSQL): to_char(MyDate, 'HH12') [note (HH12): zero-padded] [alias: 'HH'] [WARNING: 'HH' is ambiguous, 'HH12' is preferable] SQL (SQLite): strftime('%l', MyDate) [note (%l): space-padded] [also (zero-padded): '%I'] Swift: UFL: DateGetAmPm [e.g. 'AM'/'PM'] AutoHotkey: vAmPm := FormatTime(vDate, "tt") [e.g. AM/PM] [note: 't' for short version (e.g. A/P)] C++: C#: Crystal: vAmPm = oDate.to_s("%p") [WARNING: confusingly: %p returns upper case, %P returns lower case] Excel: =TEXT(A1,"AM/PM") Excel VBA: vAmPm = Format(oDate, "AM/PM") Go: Java: vAmPm = oDate.format(java.time.format.DateTimeFormatter.ofPattern("a")) JavaScript: vAmPm = oDate.getUTCHours()<12?"AM":"PM" [also: oDate.getHours()<12?"AM":"PM"] Kotlin: vAmPm = oDate.format(java.time.format.DateTimeFormatter.ofPattern("a")) PHP: $vAmPm = $oDate->format("A") [also (lower case): 'a'] Python: vAmPm = oDate.strftime("%p") [WARNING: confusingly: %p returns upper case, %P returns lower case] R: Ruby: vAmPm = oDate.strftime("%p") [WARNING: confusingly: %p returns upper case, %P returns lower case] Rust: Scala: vAmPm = oDate.format(java.time.format.DateTimeFormatter.ofPattern("a")) SQL (MySQL): date_format(MyDate, '%p') SQL (PostgreSQL): to_char(MyDate, 'AM') [note: 'AM'/'am'/'A.M.'/'a.m.' equivalent to 'PM'/'pm'/'P.M.'/'p.m.' respectively] SQL (SQLite): strftime('%p', MyDate) [WARNING: confusingly: %p returns upper case, %P returns lower case] Swift: UFL: DateGetDaySuffix [get day and suffix, or suffix only][e.g. 1st/2nd/3rd/4th][ordinal suffixes][see also: OrdinalSuffix] AutoHotkey: ___ C++: C#: Crystal: ___ Excel: ___ Excel VBA: ___ Go: Java: ___ JavaScript: ___ Kotlin: ___ PHP: $vDayAndSfx = $oDate->format("jS") [note: returns month day and English suffix e.g. '1st'] [also: month day (zero-padded) and English suffix e.g. '01st': 'dS'] Python: ___ R: Ruby: ___ Rust: Scala: ___ SQL (MySQL): date_format(MyDate, '%D') [note: returns month day and English suffix e.g. '1st'] SQL (PostgreSQL): to_char(MyDate, 'DDth') [note: returns month day (zero-padded) and English suffix e.g. '01st'] SQL (SQLite): ___ Swift: UFL: DateGetYearDay [or DateGetYDay][get the 'year day' ('day of the year')][i.e. 1-366] AutoHotkey: vYDay := FormatTime(vDate, "YDay") [also (zero-padded): 'YDay0'] C++: C#: Crystal: vYDay = oDate.day_of_year [also: oDate.to_s("%j")] Excel: =A1-DATE(YEAR(A1),1,0) Excel VBA: vYDay = DatePart("y", oDate) [also: Format(oDate, "y")] [WARNING: in Excel sheet functions, 'y' returns the 2-digit year] Go: Java: vYDay = oDate.getDayOfYear() JavaScript: vYDay = (Date.UTC(oDate.getFullYear(), oDate.getMonth(), oDate.getDate()) - Date.UTC(oDate.getFullYear(), 0, 0)) / 86400000 Kotlin: vYDay = oDate.getDayOfYear() PHP: $vYDay = 1+$oDate->format("z") [note: '1+' to convert 0-based to 1-based] [WARNING: 'z' returns a 0-based year day (0-364 in common years, 0-365 in leap years), most programming languages return a 1-based year day] Python: vYDay = oDate.timetuple().tm_yday [also: oDate.strftime("%j")] R: Ruby: vYDay = oDate.yday [also: oDate.strftime("%j")] Rust: Scala: vYDay = oDate.getDayOfYear() SQL (MySQL): dayofyear(MyDate) SQL (PostgreSQL): date_part('doy', MyDate) [note: a double, so no leading zeros] [also (zero-padded): to_char(MyDate, 'DDD')] [also (zero-padded): 'IDDD' for nth day of year based on ISO week numbering] SQL (SQLite): strftime('%j', MyDate) Swift: UFL: (DateGetEra) [or DateGetBcAd][e.g. BC/AD][note: AD starts in year 1, not year 0, but we use '>=0' not '>=1' for safety/clarity] AutoHotkey: vEra := FormatTime(vDate, "gg") [e.g. 'A.D.'] C++: C#: Crystal: ___ [can use: vEra = (oDate.year>=0) ? "AD" : "BC"] Excel: ___ [can use: =IF(YEAR(A1)>=0,"AD","BC")] Excel VBA: ___ [can use: vEra = IIf(Year(oDate) >= 0, "AD", "BC")] Go: Java: ___ [can use (CE as string e.g. 'AD', can replace CE with BCE e.g. 'BC'): java.time.chrono.IsoEra.CE.getDisplayName(java.time.format.TextStyle.SHORT, java.util.Locale.getDefault())] JavaScript: vEra = oDate.toLocaleString("en", {year:"2-digit", era:"short"}).slice(3) [e.g. 'AD'] Kotlin: ___ [can use (CE as string e.g. 'AD', can replace CE with BCE e.g. 'BC'): java.time.chrono.IsoEra.CE.getDisplayName(java.time.format.TextStyle.SHORT, java.util.Locale.getDefault())] PHP: ___ [can use: $vEra = ($oDate->format("year") >= 0) ? "AD" : "BC"] Python: ___ [can use: vEra = "AD" if (oDate.year>=0) else "BC"] R: Ruby: ___ [can use: vEra = (oDate.year>=0) ? "AD" : "BC"] Rust: Scala: ___ [can use (CE as string e.g. 'AD', can replace CE with BCE e.g. 'BC'): java.time.chrono.IsoEra.CE.getDisplayName(java.time.format.TextStyle.SHORT, java.util.Locale.getDefault())] SQL (MySQL): if(year(MyDate)>=0,'AD','BC') SQL (PostgreSQL): to_char(MyDate, 'AD') [note: 'BC'/'bc'/'B.C.'/'b.c.' equivalent to 'AD'/'ad'/'A.D.'/'a.d.' respectively] SQL (SQLite): ___ [can use: iif(strftime('%Y', MyDate)+0>=0,'AD','BC')] Swift: UFL: DateGetMonthEnd [get last day of month] AutoHotkey: ___ [can use: DateAdd and SubStr: get first day of current month, add 31 days to fall inside new month, get the day of the new month, subtract that many days] C++: C#: Crystal: oDateNew = oDate.at_end_of_month Excel: =EOMONTH(A1,0) Excel VBA: oDateNew = CDate(WorksheetFunction.EoMonth(oDate, 0)) [also: DateSerial(Year(oDate), Month(oDate) + 1, 0) (note: '0th' day of month, goes back 1 day)] Go: Java: oDateNew = oDate.with(java.time.temporal.TemporalAdjusters.lastDayOfMonth()).atStartOfDay(java.time.ZoneOffset.UTC) [note: lastDayOfMonth() returns a LocalDate] JavaScript: oDateNew = new Date(oDate.getFullYear(), oDate.getMonth()+1, 0) Kotlin: oDateNew = oDate.with(java.time.temporal.TemporalAdjusters.lastDayOfMonth()).atStartOfDay(java.time.ZoneOffset.UTC) [note: lastDayOfMonth() returns a LocalDate] PHP: $oDateNew = date_create_immutable($oDate->format("Y-m-t 23:59:59.999999")) [also (modifies date, preserves time): $oDate = $oDateNew->modify("last day of this month")] [note: format() with 't' returns the month's day count] Python: oDateNew = calendar.monthrange(oDate.year, oDate.month)[1] R: Ruby: ___ [can use: oDateNew = Time.utc(oDate.year, oDate.month) + 31*86400; oDateNew -= oDateNew.mday*86400] Rust: Scala: oDateNew = oDate.`with`(java.time.temporal.TemporalAdjusters.lastDayOfMonth()).atStartOfDay(java.time.ZoneOffset.UTC) [note: lastDayOfMonth() returns a LocalDate] SQL (MySQL): last_day(MyDate) SQL (PostgreSQL): (date_trunc('month', MyDate) + '1 month - 1 day'::interval)::timestamp [also: use '1 month - 1 second' to get '23:59:59'] SQL (SQLite): datetime(MyDate,'start of month','+1 month','-1 day') [WARNING: sets HH:MM:SS to '00:00:00'] [note: use date() to omit time values] Swift: UFL: DateGetQuarter [i.e. Jan/Feb/Mar to 1, Apr/May/Jun to 2, Jul/Aug/Sep to 3, Oct/Nov/Dec to 4] AutoHotkey: vQtr := (FormatTime(vDate, "M")+2) // 3 C++: C#: Crystal: vQtr = (oDate.month+2) // 3 [note: floor division] Excel: =INT((MONTH(A1)+2)/3) Excel VBA: vQtr = DatePart("q", oDate) [also: Format(oDate, "q")] [WARNING: Excel sheet functions lack the 'q' option] Go: Java: vQtr = oDate.get(java.time.temporal.IsoFields.QUARTER_OF_YEAR) JavaScript: vQtr = Math.floor(oDate.getUTCMonth()/3+1) [also: Math.floor(oDate.getMonth()/3+1)] [WARNING: getUTCMonth/getMonth are 0-based] Kotlin: vQtr = oDate.get(java.time.temporal.IsoFields.QUARTER_OF_YEAR) PHP: $vQtr = intdiv($oDate->format("m")+2, 3) [also: (int)ceil($oDate->format("m")/3)] [WARNING: in some programming languages, '/' does truncated division, so the ceil algorithm would fail] Python: vQtr = (oDate.month+2) // 3 R: Ruby: vQtr = (oDate.month+2) / 3 [note: floor division] Rust: Scala: vQtr = oDate.get(java.time.temporal.IsoFields.QUARTER_OF_YEAR) SQL (MySQL): quarter(MyDate) SQL (PostgreSQL): date_part('quarter', MyDate) [also: to_char(MyDate, 'Q')] SQL (SQLite): (strftime('%m', MyDate)+2) / 3 Swift: UFL: DateGetWorkdayCount [or DateGetWeekdayCount][count weekdays in date range, specify a list of dates to exclude] AutoHotkey: ___ C++: C#: Crystal: ___ Excel: =NETWORKDAYS(A1,B1) Excel VBA: vCountDays = WorksheetFunction.NetworkDays(oDate1, oDate2) Go: Java: ___ JavaScript: ___ Kotlin: ___ PHP: ___ Python: ___ R: Ruby: ___ Rust: Scala: ___ SQL (MySQL): ___ SQL (PostgreSQL): ___ SQL (SQLite): ___ Swift: UFL: DateIsLeapYear [for a date, is the year a leap year][algorithm: in a leap year, the year day for 31st Dec is 366, and for 1st Mar is 61][algorithm: in a leap year, the day after 28th Feb, is 29th Feb] AutoHotkey: vIsLeapYear := !Mod(vYear, 4) && (Mod(vYear, 100) || !Mod(vYear, 400)) [beforehand: vYear := FormatTime(vDate, "yyyy")] C++: C#: Crystal: vIsLeapYear = Time.leap_year?(vYear) [also: vIsLeapYear = (Time.days_in_year(vYear) == 366)] [beforehand: vYear = oDate.year] Excel: =IF(OR(MOD(A1,400)=0,AND(MOD(A1,4)=0,MOD(A1,100)<>0)),True,False) [note: where A1 contains a year] [note: date to year: YEAR(A1)] [note: based on code from learn.microsoft.com] Excel VBA: vIsLeapYear = (Month(DateSerial(vYear, 2, 29)) = 2) [beforehand: vYear = Year(oDate)] Go: Java: vIsLeapYear = java.time.Year.isLeap(vYear) [also: vIsLeapYear = (java.time.Year.of(vYear).length() == 366)] [beforehand: vYear = oDate.getYear()] JavaScript: vIsLeapYear = (new Date(vYear, 2-1, 29).getDate() == 29) [beforehand: vYear = oDate.getFullYear()] Kotlin: vIsLeapYear = java.time.Year.isLeap(vYear) [also: vIsLeapYear = (java.time.Year.of(vYear).length() == 366)] [beforehand: vYear = oDate.getYear()] PHP: $vIsLeapYear = !!$oDate->format("L") Python: vIsLeapYear = calendar.isleap(vYear) [beforehand: vYear = oDate.year] R: Ruby: vIsLeapYear = (Time.new(vYear, 12, 31).yday == 366) [beforehand: vYear = oDate.year] Rust: Scala: vIsLeapYear = java.time.Year.isLeap(vYear) [also: vIsLeapYear = (java.time.Year.of(vYear).length() == 366)] [beforehand: vYear = oDate.getYear()] SQL (MySQL): (day(makedate(MyYear, 60)) = 29) SQL (PostgreSQL): (date_part('doy', (MyYear||'-12-31')::timestamp) = 366) [also: (to_char((MyYear||'-12-31')::timestamp, 'DDD') = '366')] SQL (SQLite): (strftime('%j', MyYear||'-12-31') == '366') [also: (strftime('%d', MyYear||'-02-28', '1 day') == '29')] Swift: UFL: DateSetPart [or DateSetValue][e.g. set Y/M/D/H/M/S][see also: DateGetMonthEnd/DateFloor/DateCeil] AutoHotkey: ___ [can use: e.g. set day: vDate := FormatTime(vDate, Format("yyyyMM{:02}HHmmss", vDay))] [also (overwrite part of a date string): StrPut(vNumStr, StrPtr(vDateStr)+vOffset, StrLen(vNumStr))] [e.g. set all parts: vDate := Format("{:04}{:02}{:02}{:02}{:02}{:02}", vYear, vMonth, vDay, vHour, vMin, vSec)] C++: C#: Crystal: ___ [e.g. set day: oDateNew = oDate + Time::Span.new(days:vDay-oDate.day)] [e.g. set day: oDateNew = oDate + (vDay-oDate.day).days] Excel: ___ [can use: e.g. set day: =DATE(YEAR(A1),MONTH(A1),vDay)+MOD(A1,1)] [also: INT(A1) gets the date value, A1-INT(A1) or MOD(A1,1) gets the time value] [e.g. set all parts: =DATE(vYear,vMonth,vDay)+TIME(vHour,vMin,vSec)+vMSec/(86400*1000)] Excel VBA: ___ [can use: e.g. set day: oDateNew = DateSerial(Year(oDate), Month(oDate), vDay) + (oDate - Int(oDate))] [also: Int(oDate) gets the date value, oDate-Int(oDate) gets the time value] [WARNING: unlike the Excel sheet function MOD, Mod in VBA rounds floats to integers] [e.g. set all parts: oDate = DateSerial(vYear, vMonth, vDay) + TimeSerial(vHour, vMin, vSec) + vMSec / (86400 * 1000)] Go: Java: oDateNew = oDate.with(oField, vNum) [e.g. set day: oDate = oDate.with(java.time.temporal.ChronoField.DAY_OF_MONTH, vDay)] [note: plus() uses 'long, TemporalUnit', with() uses 'TemporalField, long'] [also: truncatedTo()] JavaScript: ___ [can use: e.g. set day (of month): oDate.setUTCDate(vDay)] [e.g. set day (of month): oDate.setDate(vDay)] Kotlin: oDateNew = oDate.with(oField, vNum) [e.g. set day: oDate = oDate.with(java.time.temporal.ChronoField.DAY_OF_MONTH, vDay.toLong())] [note: plus() uses 'long, TemporalUnit', with() uses 'TemporalField, long'] [also: truncatedTo()] PHP: ___ [e.g. set day: $oDateNew = $oDate->modify(($vDay-$oDate->format("d")) . " days")] Python: ___ [e.g. set day: oDate.replace(day=vDay)] [note: datetime.replace(year=self.year, month=self.month, day=self.day, hour=self.hour, minute=self.minute, second=self.second, microsecond=self.microsecond, tzinfo=self.tzinfo, *, fold=0)] [note: all arguments can be omitted] [note: 'Parameters after “*” or “*identifier” are keyword-only parameters and may only be passed by keyword arguments.'] R: Ruby: ___ [e.g. set day: oDate + (vDay-oDate.mday)*86400] Rust: Scala: oDateNew = oDate.`with`(oField, vNum) [e.g. set day: oDate = oDate.`with`(java.time.temporal.ChronoField.DAY_OF_MONTH, vDay.toLong)] [note: plus() uses 'long, TemporalUnit', with() uses 'TemporalField, long'] [also: truncatedTo()] SQL (MySQL): timestampadd(MyUnit, MyNum-extract(MyUnit from MyDate), MyDate) [e.g. set day: timestampadd(DAY, MyNum-extract(DAY from MyDate), MyDate)] SQL (PostgreSQL): ___ [e.g set day to 3: date_add(MyDate, concat(3-date_part('day', MyDate), ' days')::interval)] [also (days): date_add(MyDate, make_interval(days => 3 - date_part('day', MyDate)::int))] [also: date_trunc()] [note: '=>' indicates assigning to a named parameter] SQL (SQLite): ___ [can use: e.g. set day to 3: datetime(MyDate, strftime('-%d days', MyDate), '3 days')] [also: modifiers e.g. 'start of day', 'start of month', 'start of year'] Swift: UFL: DateSetYear [date set year (e.g. a 4-digit number)][WARNING: this could create an invalid date, throw, or wraparound up/down to the next valid date][see also: DateMakeValidRange] AutoHotkey: vDate := FormatTime(vDate, Format("{:04}MMddHHmmss", vYear)) C++: C#: Crystal: oDateNew = oDate.shift(years:vYear-oDate.year) [also: oDateNew = oDate + (vYear-oDate.year).years] [note: uses floor algorithm] Excel: =DATE(vYear,MONTH(A1),DAY(A1))+MOD(A1,1) Excel VBA: oDateNew = DateSerial(vYear, Month(oDate), Day(oDate)) + (oDate - Int(oDate)) Go: Java: oDateNew = oDate.withYear(vYear) [e.g. 2000-02-29 with year 2001 returns 2001-02-28] JavaScript: oDate.setUTCFullYear(vYear) [also: oDate.setFullYear(vYear)] [e.g. 2000-02-29 with year 2001 returns 2001-03-01] [WARNING: uses ceil algorithm for invalid dates] Kotlin: oDateNew = oDate.withYear(vYear) [e.g. 2000-02-29 with year 2001 returns 2001-02-28] PHP: ___ [can use: $oDateNew = $oDate->modify(($vYear-$oDate->format("Y")) . " years"); $oDateNew = $oDateNew->modify($oDateNew->format("d") == $oDate->format("d") ? "0 days" : "-" . $oDateNew->format("d") . "days");] Python: oDateNew = oDate.replace(year=vYear) [e.g. 2000-02-29 with year 2001 throws] R: Ruby: ___ [can use: oArray = oDate.to_a; oArray[5] = vYear; oDateNew = Time.utc(*oArray); oDateNew -= (oDateNew.mday == oDate.mday) ? 0 : oDateNew.mday*86400] [note: can replace 'oDateNew.mday*86400' with '86400' if adding years, but not if adding months] Rust: Scala: oDateNew = oDate.withYear(vYear) [e.g. 2000-02-29 with year 2001 returns 2001-02-28] SQL (MySQL): timestampadd(YEAR, MyNum-year(MyDate), MyDate) [also: timestamp(date_format(MyDate, concat(MyNum, '-%m-%d %T')))] SQL (PostgreSQL): date_add(MyDate, concat(MyNum-date_part('year', MyDate), ' years')::interval) SQL (SQLite): ___ Swift: UFL: DateSetMonth [date set month (1-12) (some systems use 0-11)][WARNING: this could create an invalid date, throw, or wraparound up/down to the next valid date][see also: DateMakeValidRange] AutoHotkey: vDate := FormatTime(vDate, Format("yyyy{:02}ddHHmmss", vMonth)) C++: C#: Crystal: oDateNew = oDate.shift(months:vMonth-oDate.month) [also: oDateNew = oDate + (vMonth-oDate.month).months] [note: uses floor algorithm] Excel: =DATE(YEAR(A1),vMonth,DAY(A1))+MOD(A1,1) Excel VBA: oDateNew = DateSerial(Year(oDate), vMonth, Day(oDate)) + (oDate - Int(oDate)) Go: Java: oDateNew = oDate.withMonth(vMonth) [e.g. 2000-01-31 with month 2 returns 2000-02-29] JavaScript: oDate.setUTCMonth(vMonth) [also: oDate.setMonth(vMonth)] [e.g. 2000-01-31 with month 2 returns 2000-03-02] [WARNING: setUTCMonth()/setMonth() are 0-based, e.g. Jan is 1-1=0, e.g. Dec is 12-1=11] [WARNING: uses ceil algorithm for invalid dates] Kotlin: oDateNew = oDate.withMonth(vMonth) [e.g. 2000-01-31 with month 2 returns 2000-02-29] PHP: ___ [can use: $oDateNew = $oDate->modify(($vMonth-$oDate->format("m")) . " months"); $oDateNew = $oDateNew->modify($oDateNew->format("d") == $oDate->format("d") ? "0 days" : "-" . $oDateNew->format("d") . "days");] [can use (specify month name): e.g. $oDateNew = $oDate->modify("July")] Python: oDateNew = oDate.replace(month=vMonth) [e.g. 2000-01-31 with month 2 throws] R: Ruby: ___ [can use: oArray = oDate.to_a; oArray[4] = vMonth; oDateNew = Time.utc(*oArray); oDateNew -= (oDateNew.mday == oDate.mday) ? 0 : oDateNew.mday*86400] Rust: Scala: oDateNew = oDate.withMonth(vMonth) [e.g. 2000-01-31 with month 2 returns 2000-02-29] SQL (MySQL): timestampadd(MONTH, MyNum-month(MyDate), MyDate) [also (handles 1 or 2 digits): timestamp(date_format(MyDate, concat('%Y-', MyNum, '-%d %T')))] SQL (PostgreSQL): date_add(MyDate, concat(MyNum-date_part('month', MyDate), ' months')::interval) SQL (SQLite): ___ Swift: UFL: DateSetDay [date set day of the month (1-31)][WARNING: this could create an invalid date, throw, or wraparound up/down to the next valid date][see also: DateMakeValidRange] AutoHotkey: vDate := FormatTime(vDate, Format("yyyyMM{:02}HHmmss", vDay)) C++: C#: Crystal: oDateNew = oDate + Time::Span.new(days:vDay-oDate.day) [also: oDateNew = oDate + (vDay-oDate.day).days] Excel: =DATE(YEAR(A1),MONTH(A1),vDay)+MOD(A1,1) Excel VBA: oDateNew = DateSerial(Year(oDate), Month(oDate), vDay) + (oDate - Int(oDate)) Go: Java: oDateNew = oDate.withDayOfMonth(vDay) [e.g. 2001-02-01 with day 29 throws] JavaScript: oDate.setUTCDate(vDay) [also: oDate.setDate(vDay)] [e.g. 2001-02-01 with day 29 returns 2001-03-01] [WARNING: uses ceil algorithm for invalid dates] Kotlin: oDateNew = oDate.withDayOfMonth(vDay) [e.g. 2001-02-01 with day 29 throws] PHP: $oDateNew = $oDate->modify(($vDay-$oDate->format("d")) . " days") [also: $oDateNew = $oDate->modify("last day of last month")->modify($vDays . " days")] Python: oDateNew = oDate.replace(day=vDay) [e.g. 2001-02-01 with day 29 throws] R: Ruby: oDateNew = oDate + (vDay-oDate.mday)*86400 Rust: Scala: oDateNew = oDate.withDayOfMonth(vDay) [e.g. 2001-02-01 with day 29 throws] SQL (MySQL): timestampadd(DAY, MyNum-day(MyDate), MyDate) [also (handles 1 or 2 digits): timestamp(date_format(MyDate, concat('%Y-%m-', MyNum, ' %T')))] SQL (PostgreSQL): date_add(MyDate, concat(MyNum-date_part('day', MyDate), ' days')::interval) SQL (SQLite): ___ [can use: e.g. set day to 3: datetime(MyDate, strftime('-%d days', MyDate), '3 days')] Swift: UFL: DateSetHours [date set hour (0-23)] AutoHotkey: vDate := FormatTime(vDate, Format("yyyyMMdd{:02}mmss", vHour)) C++: C#: Crystal: oDateNew = oDate + Time::Span.new(hours:vHour-oDate.hour) [also: oDateNew = oDate + (vHour-oDate.hour).hours] Excel: =TIME(vHour,MINUTE(A1),SECOND(A1))+INT(A1) Excel VBA: oDateNew = TimeSerial(vHour, Minute(oDate), Second(oDate)) + Int(oDate) Go: Java: oDateNew = oDate.withHour(vHour) JavaScript: oDate.setUTCHours(vHour) [also: oDate.setHours(vHour)] Kotlin: oDateNew = oDate.withHour(vHour) PHP: $oDateNew = $oDate->modify(($vHour-$oDate->format("H")) . " hours") Python: oDateNew = oDate.replace(hour=vHour) R: Ruby: oDateNew = oDate + (vHour-oDate.hour)*3600 Rust: Scala: oDateNew = oDate.withHour(vHour) SQL (MySQL): timestampadd(HOUR, MyNum-hour(MyDate), MyDate) [also (handles 1 or 2 digits): timestamp(date_format(MyDate, concat('%Y-%m-%d ', MyNum, ':%i:%S')))] SQL (PostgreSQL): date_add(MyDate, concat(MyNum-date_part('hour', MyDate), ' hours')::interval) SQL (SQLite): ___ [can use: e.g. set hour to 3: datetime(MyDate, strftime('-%H hours', MyDate), '3 hours')] Swift: UFL: DateSetMinutes [date set minute (0-59)] AutoHotkey: vDate := FormatTime(vDate, Format("yyyyMMddHH{:02}ss", vMin)) C++: C#: Crystal: oDateNew = oDate + Time::Span.new(minutes:vMin-oDate.minute) [also: oDateNew = oDate + (vMin-oDate.minute).minutes] Excel: =TIME(HOUR(A1),vMin,SECOND(A1))+INT(A1) Excel VBA: oDateNew = TimeSerial(Hour(oDate), vMin, Second(oDate)) + Int(oDate) Go: Java: oDateNew = oDate.withMinute(vMin) JavaScript: oDate.setUTCMinutes(vMin) [also: oDate.setMinutes(vMin)] Kotlin: oDateNew = oDate.withMinute(vMin) PHP: $oDateNew = $oDate->modify(($vMin-$oDate->format("i")) . " mins") Python: oDateNew = oDate.replace(minute=vMin) R: Ruby: oDateNew = oDate + (vMin-oDate.min)*60 Rust: Scala: oDateNew = oDate.withMinute(vMin) SQL (MySQL): timestampadd(MINUTE, MyNum-minute(MyDate), MyDate) [also (handles 1 or 2 digits): timestamp(date_format(MyDate, concat('%Y-%m-%d %H:', MyNum, ':%S')))] SQL (PostgreSQL): date_add(MyDate, concat(MyNum-date_part('minute', MyDate), ' minutes')::interval) SQL (SQLite): ___ [can use: e.g. set minute to 3: datetime(MyDate, strftime('-%M minutes', MyDate), '3 minutes')] Swift: UFL: DateSetSeconds [date set second (0-59)] AutoHotkey: vDate := FormatTime(vDate, Format("yyyyMMddHHmm{:02}", vSec)) C++: C#: Crystal: oDateNew = oDate + Time::Span.new(seconds:vSec-oDate.second) [also: oDateNew = oDate + (vSec-oDate.second).seconds] Excel: =TIME(HOUR(A1),MINUTE(A1),vSec)+INT(A1) Excel VBA: oDateNew = TimeSerial(Hour(oDate), Minute(oDate), vSec) + Int(oDate) Go: Java: oDateNew = oDate.withSecond(vSec) JavaScript: oDate.setUTCSeconds(vSec) [also: oDate.setSeconds(vSec)] Kotlin: oDateNew = oDate.withSecond(vSec) PHP: $oDateNew = $oDate->modify(($vSec-$oDate->format("s")) . " secs") Python: oDateNew = oDate.replace(second=vSec) R: Ruby: oDateNew = oDate + (vSec-oDate.sec) Rust: Scala: oDateNew = oDate.withSecond(vSec) SQL (MySQL): timestampadd(SECOND, MyNum-second(MyDate), MyDate) [also (handles 1 or 2 digits): timestamp(date_format(MyDate, concat('%Y-%m-%d %H:%i:', MyNum)))] SQL (PostgreSQL): date_add(MyDate, concat(MyNum-date_part('second', MyDate), ' seconds')::interval) SQL (SQLite): ___ [can use: e.g. set second to 3: datetime(MyDate, strftime('-%S seconds', MyDate), '3 seconds')] Swift: UFL: DateSetMilliseconds [or DateSetMSec][date set milliseconds (0-999)] AutoHotkey: ___ C++: C#: Crystal: oDateNew = oDate + Time::Span.new(nanoseconds:(vMSec-oDate.millisecond)*1000000) Excel: =TIME(HOUR(A1),MINUTE(A1),SECOND(A1))+INT(A1)+vMSec/(86400*1000) Excel VBA: oDateNew = TimeSerial(Hour(oDate), Minute(oDate), Second(oDate)) + Int(oDate) + vMSec / (86400 * 1000) Go: Java: oDateNew = oDate.withNano(vMSec*1000000+(oDate.getNano()%1000000)) [also: oDateNew = oDate.with(java.time.temporal.ChronoField.MILLI_OF_SECOND, vMSec)] JavaScript: oDate.setUTCMilliseconds(vMSec) [also: oDate.setMilliseconds(vMSec)] Kotlin: oDateNew = oDate.withNano(vMSec*1000000+(oDate.getNano()%1000000)) [also: oDateNew = oDate.with(java.time.temporal.ChronoField.MILLI_OF_SECOND, vMSec.toLong())] PHP: $oDateNew = $oDate->modify(($vMSec-$oDate->format("v")) . " msecs") Python: oDateNew = oDate.replace(microsecond=vMSec*1000+(oDate.microsecond%1000)) R: Ruby: oDateNew = oDate + (vMSec-oDate.usec/1000)/1000.0 Rust: Scala: oDateNew = oDate.withNano(vMSec*1000000+(oDate.getNano()%1000000)) [also: oDateNew = oDate.`with`(java.time.temporal.ChronoField.MILLI_OF_SECOND, vMSec.toLong)] SQL (MySQL): timestampadd(MICROSECOND, (MyNum-(microsecond(MyDate) div 1000))*1000, MyDate)] SQL (PostgreSQL): date_add(MyDate, concat(MyNum-(date_part('milliseconds', MyDate)::int%1000), ' milliseconds')::interval) SQL (SQLite): strftime('%Y-%m-%d %H:%M:%S', MyDate) || format('.%03d', MyMSec) Swift: UFL: DateToUnix [or DateToSec/DateGetUnix][Unix dates: seconds since 1970][note: sometimes Unix dates are stated as milliseconds] AutoHotkey: vUnixSec := DateDiff(vDateUTC, 1970, "S") C++: C#: Crystal: vUnixSec = oDate.to_unix [also: oDate.to_unix_f, oDate.to_unix_ms, oDate.to_unix_ns] Excel: =INT((A1-25569)*86400) [WARNING: the calculation uses local, not UTC time] [note: 25569 is equivalent to 1970-01-01] Excel VBA: vUnixSec = Int((oDate - 25569) * 86400) [WARNING: the calculation uses local, not UTC time] [note: 25569 is equivalent to 1970-01-01] Go: Java: vUnixSec = oDate.toEpochSecond() [also: oOffset = oTZ.getRules().getOffset(oInstant)] JavaScript: vUnixSec = Math.floor(oDate.valueOf()/1000) [note: valueOf()/getTime() are interchangeable] Kotlin: vUnixSec = oDate.toEpochSecond() [also: oOffset = oTZ.getRules().getOffset(oInstant)] PHP: $vUnixSec = $oDate->getTimestamp() [also: $oDate->format("U")] Python: vUnixSec = (oDate.astimezone(datetime.timezone.utc)-datetime.datetime.fromtimestamp(0, datetime.timezone.utc)).total_seconds() [also: vUnixSec = (oDate.astimezone(datetime.timezone.utc)-datetime.datetime(1970, 1, 1, tzinfo=datetime.timezone.utc)).total_seconds()] R: Ruby: vUnixSec = oDate.to_i [also: oDate.to_f, oDate.to_r] Rust: Scala: vUnixSec = oDate.toEpochSecond() [also: oOffset = oTZ.getRules().getOffset(oInstant)] SQL (MySQL): unix_timestamp(MyDate) [WARNING: interprets the date string using the local time zone] [also (since year 0): to_days()/to_seconds()] [related (time without date): time_to_sec()] SQL (PostgreSQL): date_part('epoch', MyDate) [also: extract(epoch from MyDate)] SQL (SQLite): unixepoch(MyDate) [also: strftime('%s', MyDate)] Swift: UFL: DateFromUnix [or UnixToDate/SecToDate][Unix dates: seconds since 1970][note: sometimes Unix dates are stated as milliseconds] AutoHotkey: vDate := DateAdd(1970, vUnixSec, "S") C++: C#: Crystal: oDate = Time::UNIX_EPOCH + Time::Span.new(seconds:vUnixSec) [also: oDate = Time::UNIX_EPOCH + vUnixSec.seconds] Excel: =25569+(A1/86400) [WARNING: the calculation uses local, not UTC time] [note: 25569 is equivalent to 1970-01-01] Excel VBA: oDate = DateSerial(1970, 1, 1) + (vUnixSec / 86400) [WARNING: the calculation uses local, not UTC time] Go: Java: oDate = java.time.ZonedDateTime.ofInstant(java.time.Instant.ofEpochSecond(vUnixSec), java.time.ZoneOffset.UTC) [also: oDate = java.time.LocalDateTime.ofEpochSecond(vUnixSec, 0, java.time.ZoneOffset.UTC)] [also: oOffset = oTZ.getRules().getOffset(oInstant)] JavaScript: oDate = new Date(vUnixSec*1000) Kotlin: oDate = java.time.ZonedDateTime.ofInstant(java.time.Instant.ofEpochSecond(vUnixSec), java.time.ZoneOffset.UTC) [also: oDate = java.time.LocalDateTime.ofEpochSecond(vUnixSec, 0, java.time.ZoneOffset.UTC)] [also: oOffset = oTZ.getRules().getOffset(oInstant)] PHP: $oDate = date_create_immutable("1970-01", timezone_open("UTC"))->modify($vUnixSec . " secs") Python: oDate = datetime.datetime.fromtimestamp(vUnixSec) R: Ruby: oDate = Time.at(vUnixSec) Rust: Scala: oDate = java.time.ZonedDateTime.ofInstant(java.time.Instant.ofEpochSecond(vUnixSec), java.time.ZoneOffset.UTC) [also: oDate = java.time.LocalDateTime.ofEpochSecond(vUnixSec, 0, java.time.ZoneOffset.UTC)] [also: oOffset = oTZ.getRules().getOffset(oInstant)] SQL (MySQL): from_unixtime(MyUnixSec) [WARNING: returns a date using the local time zone] [also (since year 0): from_days()] [related (time without date): sec_to_time()] SQL (PostgreSQL): to_timestamp(MyUnixSec) SQL (SQLite): datetime(MyUnixSec, 'unixepoch') Swift: UFL: DateToJulian [or DateGetJulian][Julian dates: days since -4713-11-24 12:00:00] AutoHotkey: vJulianDay := DateDiff(vDateUTC, 1601, "S")/86400 + 2305813.5 [note: 2305813.5 is equivalent to 1601-01-01] C++: C#: Crystal: vJulianDay = oDate.to_unix_f/86400 + 2440587.5 [note: 2440587.5 is equivalent to 1970-01-01] Excel: =A1+2415018.5 [WARNING: the calculation uses local, not UTC time] [note: 2415018.5 is equivalent to 1899-12-30] Excel VBA: vJulianDay = (oDate * 1) + 2415018.5 [WARNING: the calculation uses local, not UTC time] [note: 2415018.5 is equivalent to 1899-12-30] Go: Java: vJulianDay = oDate.toEpochSecond()/86400.0 + 2440587.5 [note: 2440587.5 is equivalent to 1970-01-01] [also (long int): vJulianDay = oDate.getLong(java.time.temporal.JulianFields.JULIAN_DAY)] JavaScript: vJulianDay = oDate.valueOf()/86400000 + 2440587.5 [note: 2440587.5 is equivalent to 1970-01-01] [note: valueOf()/getTime() are interchangeable] Kotlin: vJulianDay = oDate.toEpochSecond()/86400.0 + 2440587.5 [note: 2440587.5 is equivalent to 1970-01-01] [also (long int): vJulianDay = oDate.getLong(java.time.temporal.JulianFields.JULIAN_DAY)] PHP: $vJulianDay = $oDate->getTimestamp()/86400 + 2440587.5 [note: 2440587.5 is equivalent to 1970-01-01] Python: vJulianDay = (oDate.astimezone(datetime.timezone.utc)-datetime.datetime.fromtimestamp(0, datetime.timezone.utc)).total_seconds()/86400 + 2440587.5 [note: 2440587.5 is equivalent to 1970-01-01] R: Ruby: vJulianDay = oDate.to_f/86400 + 2440587.5 [note: 2440587.5 is equivalent to 1970-01-01] Rust: Scala: vJulianDay = oDate.toEpochSecond()/86400.0 + 2440587.5 [note: 2440587.5 is equivalent to 1970-01-01] [also (long int): vJulianDay = oDate.getLong(java.time.temporal.JulianFields.JULIAN_DAY)] SQL (MySQL): unix_timestamp(MyDate)/86400 + 2440587.5 [note: 2440587.5 is equivalent to 1970-01-01] [also (since year 0): to_days()/to_seconds()] [related (time without date): time_to_sec()] SQL (PostgreSQL): date_part('julian', MyDate) [also: extract(julian from MyDate)] [also: to_char(MyDate, 'J')] [WARNING: PostgreSQL returns an int, ignoring fractions of days] [WARNING: '2000-01-01' is equivalent to 2451545.5, but PostgreSQL returns 2451545 (cuts half a day)] SQL (SQLite): julianday(MyDate) [also: strftime('%J', MyDate)] Swift: UFL: DateFromJulian [or JulianToDate][Julian dates: days since -4713-11-24 12:00:00] AutoHotkey: vDate := DateAdd(1601, vJulianDay-2305813.5, "D") [note: 2305813.5 is equivalent to 1601-01-01] C++: C#: Crystal: oDate = Time::UNIX_EPOCH + Time::Span.new(seconds:((vJulianDay-2440587.5)*86400).to_i) [also: oDate = Time::UNIX_EPOCH + ((vJulianDay-2440587.5)*86400).to_i.seconds] [note: 2440587.5 is equivalent to 1970-01-01] [WARNING: these techniques reduce accuracy to seconds, however, techniques using nanosecond spans can overflow] Excel: =A1-2415018.5 [WARNING: the calculation uses local, not UTC time] [note: 2415018.5 is equivalent to 1899-12-30] Excel VBA: oDate = DateSerial(100, 1, 1) + (vJulianDay - 1757584.5) [WARNING: the calculation uses local, not UTC time] [note: 1757584.5 is equivalent to 0100-01-01] Go: Java: oDate = java.time.ZonedDateTime.ofInstant(java.time.Instant.ofEpochSecond((long)((vJulianDay-2440587.5)*86400)), java.time.ZoneOffset.UTC) [also (LocalDateTime): oDate = java.time.LocalDateTime.ofEpochSecond((long)((vJulianDay-2440587.5)*86400), 0, java.time.ZoneOffset.UTC) [note: 2440587.5 is equivalent to 1970-01-01] [also (long int): oDate = java.time.LocalDateTime.MIN.with(java.time.temporal.JulianFields.JULIAN_DAY, (long)(vJulianDay+0.5))] JavaScript: oDate = new Date((vJulianDay-2440587.5)*86400000) [note: 2440587.5 is equivalent to 1970-01-01] Kotlin: oDate = java.time.ZonedDateTime.ofInstant(java.time.Instant.ofEpochSecond(((vJulianDay-2440587.5)*86400).toLong()), java.time.ZoneOffset.UTC) [also (LocalDateTime): oDate = java.time.LocalDateTime.ofEpochSecond(((vJulianDay-2440587.5)*86400).toLong(), 0, java.time.ZoneOffset.UTC) [note: 2440587.5 is equivalent to 1970-01-01] [also (long int): oDate = java.time.LocalDateTime.MIN.with(java.time.temporal.JulianFields.JULIAN_DAY, (vJulianDay+0.5).toLong())] PHP: $oDate = date_create_immutable("1970-01", timezone_open("UTC"))->modify((($vJulianDay-2440587.5)*86400) . " secs") Python: oDate = datetime.datetime.fromtimestamp((vJulianDay-2440587.5)*86400) [note: 2440587.5 is equivalent to 1970-01-01] R: Ruby: oDate = Time.at((vJulianDay-2440587.5)*86400) [note: 2440587.5 is equivalent to 1970-01-01] Rust: Scala: oDate = java.time.ZonedDateTime.ofInstant(java.time.Instant.ofEpochSecond(((vJulianDay-2440587.5)*86400).toLong), java.time.ZoneOffset.UTC) [also (LocalDateTime): oDate = java.time.LocalDateTime.ofEpochSecond(((vJulianDay-2440587.5)*86400).toLong, 0, java.time.ZoneOffset.UTC) [note: 2440587.5 is equivalent to 1970-01-01] [also (long int): oDate = java.time.LocalDateTime.MIN.`with`(java.time.temporal.JulianFields.JULIAN_DAY, (vJulianDay+0.5).toLong)] SQL (MySQL): from_unixtime((MyJulianDay-2440587.5)*86400) [note: 2440587.5 is equivalent to 1970-01-01] [also (since year 0): from_days()] [related (time without date): sec_to_time()] SQL (PostgreSQL): to_timestamp(MyJulianDay::text, 'J') [also: concat('J', MyJulianDay)::timestamp] [WARNING: 2451545.5 is equivalent to '2000-01-01 00:00:00', but PostgreSQL returns '2000-01-01 12:00:00' (adds half a day)] SQL (SQLite): datetime(MyJulianDay) Swift: UFL: DateRound [round date to nearest interval e.g. 15 minutes/1 hour (tiebreaker: round away from 0)] AutoHotkey: ___ C++: C#: Crystal: ___ Excel: ___ [can use (15 minutes): MROUND(A1,"0:15")] [also (15 minutes): MROUND(A1,15/(24*60))] Excel VBA: ___ Go: Java: ___ JavaScript: ___ Kotlin: ___ PHP: ___ Python: ___ R: Ruby: ___ Rust: Scala: ___ SQL (MySQL): ___ SQL (PostgreSQL): ___ SQL (SQLite): ___ Swift: UFL: DateCeil [round date up to nearest interval e.g. 15 minutes/1 hour] AutoHotkey: ___ C++: C#: Crystal: ___ Excel: ___ [can use (15 minutes): CEILING(A1,"0:15")] [also (15 minutes): CEILING(A1,15/(24*60))] [WARNING: Excel's CEILING is unusual, it rounds away from 0 (it is usual to round towards +infinity)] Excel VBA: ___ Go: Java: ___ JavaScript: ___ Kotlin: ___ PHP: ___ Python: ___ R: Ruby: ___ Rust: Scala: ___ SQL (MySQL): ___ SQL (PostgreSQL): ___ SQL (SQLite): ___ Swift: UFL: DateFloor [round date down to nearest interval e.g. 15 minutes/1 hour][see also: Mod/TruncDiv] AutoHotkey: ___ [can use (15 minutes, 14-digit dates): vDateNew := vDate - Mod(Mod(vDate, 10000), 1500)] C++: C#: Crystal: ___ [can use (15 minutes): oDateNew = Time::UNIX_EPOCH + Time::Span.new(seconds:oDate.to_unix-oDate.to_unix % (15*60))] [also (15 minutes): oDateNew = Time::UNIX_EPOCH + (oDate.to_unix-oDate.to_unix % (15*60)).seconds] Excel: ___ [can use (15 minutes): =FLOOR(A1,"0:15")] [also (15 minutes): =FLOOR(A1,15/(24*60))] [WARNING: Excel's FLOOR is unusual, it rounds towards 0 (it is usual to round towards -infinity)] Excel VBA: ___ [can use (15 minutes): oDateNew = CDate((15 / 1440) * Fix(oDate / (15 / 1440)))] [note: divide by x, truncate to the nearest integer, multiply by x] Go: Java: ___ JavaScript: ___ [can use (15 minutes): oDateNew = new Date(oDate.valueOf()-oDate.valueOf()%(15*60000))] Kotlin: ___ [can use (15 minutes): var vUnixSec = oDate.toEpochSecond(); vUnixSec -= vUnixSec % (15*60); var oDateNew = java.time.ZonedDateTime.ofInstant(java.time.Instant.ofEpochSecond(vUnixSec), oDate.getZone())] [also (15 minutes, LocalDateTime): var vUnixSec = oDate.toEpochSecond(java.time.ZoneOffset.UTC); vUnixSec -= vUnixSec % (15*60); var oDateNew = java.time.LocalDateTime.ofEpochSecond(vUnixSec, 0, java.time.ZoneOffset.UTC)] PHP: ___ [can use (15 minutes): $oDateNew = $oDate->modify("-" . $oDate->getTimestamp() % (15*60) . "secs") Python: ___ [can use (15 minutes): vUnixSec = (oDate.astimezone(datetime.timezone.utc)-datetime.datetime.fromtimestamp(0, datetime.timezone.utc)).total_seconds(); vUnixSec -= math.fmod(vUnixSec, 15*60); oDateNew = datetime.datetime.fromtimestamp(vUnixSec)] [requires: import datetime] [requires: import math] R: Ruby: ___ [can use (15 minutes): oDateNew = Time.at(oDate.to_f-oDate.to_f%(15*60))] Rust: Scala: ___ [can use (15 minutes): var vUnixSec = oDate.toEpochSecond(); vUnixSec -= vUnixSec % (15*60); var oDateNew = java.time.ZonedDateTime.ofInstant(java.time.Instant.ofEpochSecond(vUnixSec), oDate.getZone())] [also (15 minutes, LocalDateTime): var vUnixSec = oDate.toEpochSecond(java.time.ZoneOffset.UTC); vUnixSec -= vUnixSec % (15*60); var oDateNew = java.time.LocalDateTime.ofEpochSecond(vUnixSec, 0, java.time.ZoneOffset.UTC)] SQL (MySQL): ___ [can use (15 minutes): from_unixtime(unix_timestamp(MyDate)-unix_timestamp(MyDate)%(15*60))] SQL (PostgreSQL): ___ [e.g 15 minutes: date_bin('15 minutes', MyDate, '2000-01-01'::timestamp)] [related: generate_series()] SQL (SQLite): ___ [can use (15 minutes): datetime(unixepoch(MyDate)-unixepoch(MyDate)%(15*60), 'unixepoch')] Swift: UFL: DateBetween [is date between 2 dates (inclusive)] AutoHotkey: ___ C++: C#: Crystal: (oDateMin..oDateMax) === oDate [also: (oDateMin..oDateMax).includes?(oDate)] Excel: ___ Excel VBA: ___ Go: Java: ___ JavaScript: ___ Kotlin: ___ PHP: $vBool = ($oDateMin <= $oDate && $oDate <= $oDateMax) [also: $vBool = ($oDateMin->getTimestamp() <= $oDate->getTimestamp() && $oDate->getTimestamp() <= $oDateMax->getTimestamp())] Python: ___ R: Ruby: oDate.between?(oDateMin, oDateMax) Rust: Scala: ___ SQL (MySQL): ___ [can use (e.g. for dates of the form: 'YYYY-MM-DD HH:MM:SS', BETWEEN operator): (MyDate BETWEEN MyMin AND MyMax)] SQL (PostgreSQL): ___ [can use (BETWEEN operator): (MyDate BETWEEN MyMin AND MyMax)] SQL (SQLite): ___ [can use (e.g. for dates of the form: 'YYYY-MM-DD HH:MM:SS', BETWEEN operator): (MyDate BETWEEN MyMin AND MyMax)] Swift: UFL: DateGetTZOffsetMinSpecific [the offset from UTC in minutes for a given date, for a specific time zone][e.g. 60 minutes for BST (offset '+01:00')] AutoHotkey: ___ C++: C#: Crystal: (oDate.to_local_in(location:Time::Location.load(vTZ)).offset/60).to_i [also: (oDate.to_local_in(location:Time::Location.load(vTZ)).zone.offset/60).to_i] [e.g. vTZ = "Europe/London"] Excel: ___ Excel VBA: ___ Go: Java: vOffsetMin = oTZ.getRules().getOffset(oDate.toInstant()).getTotalSeconds() / 60 [e.g. oTZ = java.time.ZoneId.of("Europe/London")] [e.g. BST: 60] JavaScript: vOffsetMin = (Date.parse(oDate.toLocaleString("en",{timeZone:vTZ})+" Z")-Date.parse(oDate.toLocaleString("en",{timeZone:"UTC"})+" Z"))/60000 [note: 'Z' to treat strings as UTC] [e.g. BST: 60] [e.g. vTZ = "Europe/London"] Kotlin: vOffsetMin = oTZ.getRules().getOffset(oDate.toInstant()).getTotalSeconds() / 60 [e.g. oTZ = java.time.ZoneId.of("Europe/London")] [e.g. BST: 60] PHP: $oDate->setTimezone(timezone_open($vTZ))->getOffset()/60 [e.g. $vTZ = "Europe/London"] Python: vOffsetMin = oDate.astimezone(oTZ).utcoffset().total_seconds() // 60 [e.g. BST: 60] [e.g. oTZ = zoneinfo.ZoneInfo("Europe/London")] [also: MAJOR WARNING: uses *current* local offset, i.e. assumes *current* DST state for *all* dates: local time zone, current offset: e.g. oTZ = datetime.datetime.now().astimezone().tzinfo] R: Ruby: ___ [can use (UTC date): vTZDefault = ENV["TZ"]; ENV["TZ"] = vTZ; vOffsetMin = oDate.getlocal.utc_offset / 60; ENV["TZ"] = vTZDefault] [e.g. vTZ = "Europe/London"] Rust: Scala: oTZ.getRules().getOffset(oDate.toInstant()).getTotalSeconds() / 60 [e.g. oTZ = java.time.ZoneId.of("Europe/London")] [e.g. BST: 60] SQL (MySQL): ___ [e.g. round(-timestampdiff(SECOND, convert_tz(MyDate, '+00:00', 'Europe/London'), MyDate) / 60)] [e.g. BST: 60] [WARNING: timestampdiff() returns oDate2-oDate1 (invert it via '-' for the more intuitive/common oDate1-oDate2)] [e.g. replace MyDate with utc_timestamp()] SQL (PostgreSQL): ___ [can use: BEGIN; SET LOCAL timezone = 'Europe/London'; SELECT round(extract(epoch from ('2006-05-04 03:02:01'::timestamp - '2006-05-04 03:02:01'::timestamptz AT TIME ZONE 'UTC')::interval) / 60); COMMIT;] [note: the time zone is set only within the transaction] [can use (offset, not location): round(extract(epoch from ('2006-05-04 03:02:01+01'::timestamp - '2006-05-04 03:02:01+01'::timestamptz AT TIME ZONE 'UTC')::interval) / 60)] [MAJOR WARNING: this (the following) simply returns the *local* time zone, not the timestamp time zone: round(extract(timezone from '2006-05-04 03:02:01+01'::timestamptz) / 60)] SQL (SQLite): ___ Swift: UFL: DateGetTZOffsetMinLocal [the offset from UTC in minutes for a date object, for the local (session/system) time zone offset in force at the time][e.g. 60 minutes for BST (offset '+01:00')][WARNING: if using a raw offset, not a location, be aware of converting a UTC date to local with DST, when DST was not in force (or UTC to local without DST, when DST was in force)][e.g. convert a date object to a local date object, then get the value] AutoHotkey: ___ [can use (current offset): vOffsetMin := Round(DateDiff(A_Now, A_NowUTC, "S")/60)] [e.g. BST: 60] C++: C#: Crystal: (oDate.to_local.offset/60).to_i [also: (oDate.to_local.zone.offset/60).to_i] Excel: ___ Excel VBA: ___ Go: Java: vOffsetMin = oTZ.getRules().getOffset(oDate.toInstant()).getTotalSeconds() / 60 [beforehand: oTZ = java.time.ZoneId.systemDefault()] [e.g. BST: 60] JavaScript: vOffsetMin = oDate.getTimezoneOffset() [e.g. BST: 60] [e.g. new Date().getTimezoneOffset()] [e.g. new Date(2006, 7-1, 1).getTimezoneOffset()] Kotlin: vOffsetMin = oTZ.getRules().getOffset(oDate.toInstant()).getTotalSeconds() / 60 [beforehand: oTZ = java.time.ZoneId.systemDefault()] [e.g. BST: 60] PHP: $oDate->setTimezone(timezone_open(date_default_timezone_get()))->getOffset()/60 Python: ___ [can use (uses current offset for all dates): vOffsetMin = oDate.astimezone().utcoffset().total_seconds() // 60] [MAJOR WARNING: uses *current* local offset, i.e. assumes *current* DST state for *all* dates] [e.g. BST: 60] [also (to get both offsets): time.timezone//60 and time.altzone//60 (e.g. they return 0 and -60 respectively for Europe/London)] [WARNING: time.altzone() returns -60, not 60, for BST] R: Ruby: oDate.utc_offset / 60 [also (UTC date): oDate.getlocal.utc_offset / 60] [also (date in non-local time zone): oDate.getutc.getlocal.utc_offset / 60] Rust: Scala: vOffsetMin = oTZ.getRules().getOffset(oDate.toInstant()).getTotalSeconds() / 60 [beforehand: oTZ = java.time.ZoneId.systemDefault()] [e.g. BST: 60] SQL (MySQL): ___ [e.g. round(-timestampdiff(SECOND, convert_tz(MyDate, '+00:00', 'Europe/London'), MyDate) / 60)] [e.g. current offset: round(-timestampdiff(SECOND, now(), utc_timestamp()) / 60)] [e.g. BST: 60] [WARNING: timestampdiff() returns oDate2-oDate1 (invert it via '-' for the more intuitive/common oDate1-oDate2)] SQL (PostgreSQL): ___ [can use: round(extract(epoch from ('2006-05-04 03:02:01'::timestamp - '2006-05-04 03:02:01'::timestamptz AT TIME ZONE 'UTC')::interval) / 60)] [can use (current offset): (extract(timezone from now()) / 60)::int] SQL (SQLite): round((unixepoch(datetime(MyDate, 'localtime')) - unixepoch(datetime(MyDate))) / 60.0) [e.g. BST: 60] Swift: UFL: DateGetTZOffsetMin [for a date object, the offset from UTC in minutes for the date and time zone][e.g. 60 minutes for BST (offset '+01:00')] AutoHotkey: ___ C++: C#: Crystal: (oDate.offset/60).to_i [also: (oDate.zone.offset/60).to_i] Excel: ___ Excel VBA: ___ Go: Java: vOffsetMin = oDate.getOffset().getTotalSeconds() / 60 [e.g. BST: 60] JavaScript: ___ Kotlin: vOffsetMin = oDate.getOffset().getTotalSeconds() / 60 [e.g. BST: 60] PHP: $oDate->getOffset()/60 [also: $oDate->format("Z")/60] Python: vOffsetMin = oDate.utcoffset().total_seconds() // 60 [e.g. BST: 60] R: Ruby: oDate.utc_offset / 60 Rust: Scala: vOffsetMin = oDate.getOffset().getTotalSeconds() / 60 [e.g. BST: 60] SQL (MySQL): ___ [can use: round(time_to_sec(replace(right(MyDate, 6), '+', '')) / 60)] [e.g. where MyDate equals '2006-05-04T03:02:01+01:00', apply time_to_sec() to the last 6 chars] [WARNING: time_to_sec() accepts a leading '-', but not a leading '+'] [e.g. BST: 60] SQL (PostgreSQL): ___ [can use (offset, not location): round(extract(epoch from ('2006-05-04 03:02:01+01'::timestamp - '2006-05-04 03:02:01+01'::timestamptz AT TIME ZONE 'UTC')::interval) / 60)] [MAJOR WARNING: this simply returns the *local* time zone, not the timestamp time zone: round(extract(timezone from '2006-05-04 03:02:01+01'::timestamptz) / 60)] SQL (SQLite): ___ Swift: UFL: DateGetTZOffsetStrSpecific [the offset from UTC as a string for a given date, for a specific time zone][e.g. '+01:00'/'+0100'] AutoHotkey: ___ C++: C#: Crystal: oDate.to_local_in(location:Time::Location.load(vTZ)).zone.format(with_seconds:false) [e.g. vTZ = "Europe/London"] [e.g. '+00:00', '+01:00'] Excel: ___ Excel VBA: ___ Go: Java: vOffset = oTZ.getRules().getOffset(oDate.toInstant()).toString() [e.g. oTZ = java.time.ZoneId.of("Europe/London")] [e.g. 'Z' / '+01:00'] JavaScript: vOffset = oDate.toLocaleString("en", {timeZone:vTZ, day:"2-digit", timeZoneName:"longOffset"}).slice(7) || "+00:00" [e.g. vTZ = "Europe/London"] Kotlin: vOffset = oTZ.getRules().getOffset(oDate.toInstant()).toString() [e.g. oTZ = java.time.ZoneId.of("Europe/London")] [e.g. 'Z' / '+01:00'] PHP: $oDate->setTimezone(timezone_open($vTZ))->format("P") [e.g. $vTZ = "Europe/London"] [e.g. '+00:00', '+01:00'] Python: vOffset = oDate.astimezone(oTZ).strftime("%:z") [e.g. '+01:00'] [also: '%z' (e.g. '+0100')] [e.g. oTZ = zoneinfo.ZoneInfo("Europe/London")] [also: MAJOR WARNING: uses *current* local offset, i.e. assumes *current* DST state for *all* dates: local time zone, current offset: e.g. oTZ = datetime.datetime.now().astimezone().tzinfo] R: Ruby: ___ [can use (UTC date): vTZDefault = ENV["TZ"]; ENV["TZ"] = vTZ; vOffsetStr = oDate.getlocal.strftime("%:z"); ENV["TZ"] = vTZDefault] [e.g. vTZ = "Europe/London"] [e.g. '+00:00', '+01:00'] Rust: Scala: vOffset = oTZ.getRules().getOffset(oDate.toInstant()).toString() [e.g. oTZ = java.time.ZoneId.of("Europe/London")] [e.g. 'Z' / '+01:00'] SQL (MySQL): ___ [can use: left(lpad(sec_to_time(-timestampdiff(SECOND, convert_tz(MyDate, '+00:00', 'Europe/London'), MyDate)), 9, '+'), 6)] [e.g. MyDate = '2006-05-04T03:02:01'] [e.g. '+01:00'] [algorithm: sec_to_time() returns a value of the form '-00:00:00' or '00:00:00', lpad() adds a '+' if needed] [WARNING: timestampdiff() returns oDate2-oDate1 (invert it via '-' for the more intuitive/common oDate1-oDate2)] [algorithm: a longer approach for adding the '+' for non-negative values: left(replace(concat('+', sec_to_time(@vOffsetMin*60)), '+-', '-'), 6)] SQL (PostgreSQL): ___ [can use: BEGIN; SET LOCAL timezone = 'Europe/London'; SELECT left(lpad(('2006-05-04 03:02:01'::timestamp - '2006-05-04 03:02:01'::timestamptz AT TIME ZONE 'UTC')::text, 9, '+'), 6); COMMIT;] [e.g. '+01:00'] [note: the time zone is set only within the transaction] [can use (offset, not location): e.g. left(lpad(('2006-05-04 03:02:01+01'::timestamp - '2006-05-04 03:02:01+01'::timestamptz AT TIME ZONE 'UTC')::text, 9, '+'), 6)] SQL (SQLite): ___ Swift: UFL: DateGetTZOffsetStrLocal [the offset from UTC as a string for a date object, for the local (session/system) time zone offset in force at the time][e.g. '+01:00'/'+0100'][e.g. convert a date object to a local date object, then get the value] AutoHotkey: ___ C++: C#: Crystal: oDate.to_local.zone.format(with_seconds:false) [e.g. '+00:00', '+01:00'] Excel: ___ Excel VBA: ___ Go: Java: vOffset = oTZ.getRules().getOffset(oDate.toInstant()).toString() [beforehand: oTZ = java.time.ZoneId.systemDefault()] [e.g. 'Z' / '+01:00'] JavaScript: vOffset = oDate.toLocaleString("en", {day:"2-digit", timeZoneName:"longOffset"}).slice(7) || "+00:00" [e.g. oDate = new Date()] [e.g. oDate = new Date(2006, 7-1, 1)] Kotlin: vOffset = oTZ.getRules().getOffset(oDate.toInstant()).toString() [beforehand: oTZ = java.time.ZoneId.systemDefault()] [e.g. 'Z' / '+01:00'] PHP: $oDate->setTimezone(timezone_open(date_default_timezone_get()))->format("P") [e.g. '+00:00', '+01:00'] Python: vOffset = oDate.astimezone().strftime("%:z") [MAJOR WARNING: uses *current* local offset, i.e. assumes *current* DST state for *all* dates] [e.g. '+01:00'] [also: '%z' (e.g. '+0100')] R: Ruby: oDate.strftime("%:z") [also (UTC date): oDate.getlocal.strftime("%:z")] [also (date in non-local time zone): oDate.getutc.getlocal.strftime("%:z")] [e.g. '+00:00', '+01:00'] Rust: Scala: vOffset = oTZ.getRules().getOffset(oDate.toInstant()).toString() [beforehand: oTZ = java.time.ZoneId.systemDefault()] [e.g. 'Z' / '+01:00'] SQL (MySQL): ___ [can use: left(lpad(sec_to_time(-timestampdiff(SECOND, now(), utc_timestamp())), 9, '+'), 6)] [e.g. MyDate = '2006-05-04T03:02:01'] [e.g. '+01:00'] [algorithm: sec_to_time() returns a value of the form '-00:00:00' or '00:00:00', lpad() adds a '+' if needed] [WARNING: timestampdiff() returns oDate2-oDate1 (invert it via '-' for the more intuitive/common oDate1-oDate2)] SQL (PostgreSQL): ___ [can use: left(lpad(('2006-05-04 03:02:01'::timestamp - '2006-05-04 03:02:01'::timestamptz AT TIME ZONE 'UTC')::text, 9, '+'), 6)] [e.g. '+01:00'] [can use (current offset): to_char(now(), 'OF:00')] [also (current offset): left(lpad(utc_offset::text, 9, '+'), 6) FROM pg_timezone_names WHERE name = current_setting('timezone')] SQL (SQLite): ___ [can use: substr(replace(timediff(datetime(MyDate, 'localtime'), MyDate), '0000-00-00 ', ''), 1, 6)] [also (unreliable since 'localtime' used twice): substr(timediff(datetime(MyDate, 'localtime'), MyDate), 1, 1)||substr(timediff(datetime(MyDate, 'localtime'), MyDate), 13, 5)] Swift: UFL: DateGetTZOffsetStr [for a date object, the offset from UTC as a string for the date and time zone][e.g. '+01:00'/'+0100'] AutoHotkey: ___ C++: C#: Crystal: oDate.zone.format(with_seconds:false) [e.g. '+00:00', '+01:00'] Excel: ___ Excel VBA: ___ Go: Java: vOffset = oDate.getOffset().toString() [e.g. 'Z' / '+01:00'] JavaScript: ___ Kotlin: vOffset = oDate.getOffset().toString() [e.g. 'Z' / '+01:00'] PHP: $oDate->format("P") [e.g. '+00:00', '+01:00'] [also: 'p': 'The same as P, but returns Z instead of +00:00'] Python: vOffset = oDate.strftime("%:z") [e.g. '+01:00'] [also: '%z' (e.g. '+0100')] R: Ruby: oDate.strftime("%:z") [e.g. '+00:00', '+01:00'] Rust: Scala: vOffset = oDate.getOffset().toString() [e.g. 'Z' / '+01:00'] SQL (MySQL): ___ [can use: right(MyDate, 6)] [e.g. right('2006-05-04T03:02:01+01:00', 6)] SQL (PostgreSQL): ___ [can use (offset, not location): e.g. left(lpad(('2006-05-04 03:02:01+01'::timestamp - '2006-05-04 03:02:01+01'::timestamptz AT TIME ZONE 'UTC')::text, 9, '+'), 6)] [e.g. '+01:00'] SQL (SQLite): ___ Swift: UFL: DateIsDSTSpecific [was DST in force for a given date, for a specific time zone] AutoHotkey: ___ C++: C#: Crystal: oDate.to_local_in(location:Time::Location.load(vTZ)).zone.dst? [e.g. vTZ = "Europe/London"] Excel: ___ Excel VBA: ___ Go: Java: vIsDST = oTZ.getRules().isDaylightSavings(oDate.toInstant()) [e.g. oTZ = java.time.ZoneId.of("Europe/London")] JavaScript: ___ [note: toLocaleString() and Date.parse() can be used on a sample of dates (e.g. 1 date per month), to determine the DST/non-DST UTC offsets for a time zone, and that can be compared with the current UTC offset (see the 'DateGetTZOffsetMin' example)] Kotlin: vIsDST = oTZ.getRules().isDaylightSavings(oDate.toInstant()) [e.g. oTZ = java.time.ZoneId.of("Europe/London")] PHP: !!$oDate->setTimezone(timezone_open($vTZ))->format("I") [e.g. $vTZ = "Europe/London"] Python: vIsDST = (oTZ.dst(oDate).total_seconds() != 0) [e.g. oTZ = zoneinfo.ZoneInfo("Europe/London")] R: Ruby: ___ [can use (UTC date): vTZDefault = ENV["TZ"]; ENV["TZ"] = vTZ; vIsDST = oDate.getlocal.dst?; ENV["TZ"] = vTZDefault] [e.g. vTZ = "Europe/London"] Rust: Scala: vIsDST = oTZ.getRules().isDaylightSavings(oDate.toInstant()) [e.g. oTZ = java.time.ZoneId.of("Europe/London")] SQL (MySQL): ___ SQL (PostgreSQL): ___ SQL (SQLite): ___ Swift: UFL: DateIsDSTLocal [was DST in force for a date object, for the local (session/system) time zone offset in force at the time][workaround: convert the date to UTC, get the UTC/local difference in minutes, check if it's equal to the DST offset in the minutes][e.g. convert a date object to a local date object, then get the value][see also: DateGetTZOffsetMinLocal] AutoHotkey: ___ C++: C#: Crystal: oDate.to_local.zone.dst? Excel: ___ Excel VBA: ___ Go: Java: vIsDST = oTZ.getRules().isDaylightSavings(oDate.toInstant()) [beforehand: oTZ = java.time.ZoneId.systemDefault()] JavaScript: vIsDST = (oDate.getTimezoneOffset() < Math.max(new Date(oDate.getFullYear(), 6, 1).getTimezoneOffset(), new Date(oDate.getFullYear(), 0, 1).getTimezoneOffset())) [WARNING: it happens to work for almost every time zone, but is not guaranteed to work] [e.g. oDate = new Date()] [e.g. oDate = new Date(2006, 7-1, 1)] Kotlin: vIsDST = oTZ.getRules().isDaylightSavings(oDate.toInstant()) [beforehand: oTZ = java.time.ZoneId.systemDefault()] PHP: !!$oDate->setTimezone(timezone_open(date_default_timezone_get()))->format("I") Python: vIsDST = (oDate.astimezone().utcoffset().total_seconds() != -time.timezone) [MAJOR WARNING: uses *current* local offset, i.e. assumes *current* DST state for *all* dates] [algorithm: if offset is not the standard (non-DST) offset, it must be the DST offset] R: Ruby: oDate.dst? [also (UTC date): oDate.getlocal.dst?] [also (date in non-local time zone): oDate.getutc.getlocal.dst?] Rust: Scala: vIsDST = oTZ.getRules().isDaylightSavings(oDate.toInstant()) [beforehand: oTZ = java.time.ZoneId.systemDefault()] SQL (MySQL): ___ SQL (PostgreSQL): ___ [can use (current offset): is_dst FROM pg_timezone_names WHERE name = current_setting('timezone')] SQL (SQLite): ___ Swift: UFL: DateIsDST [for a date object, was DST in force for the date and time zone] AutoHotkey: ___ C++: C#: Crystal: oDate.zone.dst? Excel: ___ Excel VBA: ___ Go: Java: vIsDST = oDate.getZone().getRules().isDaylightSavings(oDate.toInstant()) JavaScript: ___ Kotlin: vIsDST = oDate.getZone().getRules().isDaylightSavings(oDate.toInstant()) PHP: !!$oDate->format("I") Python: ___ R: Ruby: oDate.dst? Rust: Scala: vIsDST = oDate.getZone().getRules().isDaylightSavings(oDate.toInstant()) SQL (MySQL): ___ SQL (PostgreSQL): ___ SQL (SQLite): ___ Swift: UFL: DateGetTZLocationLocal [get the local (session/system) time zone location][e.g. 'Europe/London', 'America/New_York'] AutoHotkey: ___ C++: C#: Crystal: vTZ = Time::Location.local.name [e.g. 'UTC', 'Europe/London'] Excel: ___ Excel VBA: ___ Go: Java: vTZ = oTZ.getId() [beforehand: oTZ = java.time.ZoneId.systemDefault()] [e.g. 'Europe/London'] JavaScript: vTZ = Intl.DateTimeFormat().resolvedOptions().timeZone [e.g. 'Europe/London'] Kotlin: vTZ = oTZ.getId() [beforehand: oTZ = java.time.ZoneId.systemDefault()] [e.g. 'Europe/London'] PHP: $vTZ = date_default_timezone_get() [e.g. 'UTC', 'Europe/London'] Python: ___ [can use (list time zone locations that match the current time zone offset name): oDate = datetime.datetime.now(); oTuple = time.tzname; oList = [vTZ for vTZ in zoneinfo.available_timezones() if zoneinfo.ZoneInfo(vTZ).tzname(oDate) in oTuple]] [requires: import datetime, time, zoneinfo] R: Ruby: vTZ = ENV["TZ"] [e.g. nil, 'Europe/London'] Rust: Scala: vTZ = oTZ.getId() [beforehand: oTZ = java.time.ZoneId.systemDefault()] [e.g. 'Europe/London'] SQL (MySQL): ___ [can use: @@time_zone] [e.g. 'SYSTEM', 'Europe/London'] SQL (PostgreSQL): current_setting('timezone') [e.g. 'Etc/UTC', 'Europe/London'] SQL (SQLite): ___ Swift: UFL: DateGetTZLocation [for a date object, get the time zone location][e.g. 'Europe/London', 'America/New_York'] AutoHotkey: ___ C++: C#: Crystal: vTZ = oDate.location.name [e.g. 'UTC', 'Europe/London'] Excel: ___ Excel VBA: ___ Go: Java: vTZ = oDate.getZone().getId() [e.g. getId: 'Europe/London'] [also: oTZ.getDisplayName(java.time.format.TextStyle.FULL, java.util.Locale.getDefault())] [e.g. getDisplayName (text style: FULL/SHORT/NARROW): 'Europe/London', 'British Time', 'BT'] JavaScript: ___ Kotlin: vTZ = oDate.getZone().getId() [e.g. getId: 'Europe/London'] [also: oTZ.getDisplayName(java.time.format.TextStyle.FULL, java.util.Locale.getDefault())] [e.g. getDisplayName (text style: FULL/SHORT/NARROW): 'Europe/London', 'British Time', 'BT'] PHP: $vTZ = $oDate->format("e") [e.g. 'UTC', 'Europe/London'] Python: ___ R: Ruby: ___ Rust: Scala: vTZ = oDate.getZone().getId() [e.g. getId: 'Europe/London'] [also: oTZ.getDisplayName(java.time.format.TextStyle.FULL, java.util.Locale.getDefault())] [e.g. getDisplayName (text style: FULL/SHORT/NARROW): 'Europe/London', 'British Time', 'BT'] SQL (MySQL): ___ SQL (PostgreSQL): ___ SQL (SQLite): ___ Swift: UFL: DateGetTZOffsetNameSpecific [get the time zone offset name for a given date, for a specific time zone][e.g. 'Greenwich Mean Time', 'British Summer Time', 'GMT', 'BST'][e.g. 'Eastern Standard Time', 'Eastern Daylight Time', 'EST', EDT'][note: the name can vary depending on whether or not DST is in force] AutoHotkey: ___ C++: C#: Crystal: oDate.to_local_in(location:Time::Location.load(vTZ)).zone.name [e.g. vTZ = "Europe/London"] [e.g. 'GMT', 'BST'] Excel: ___ Excel VBA: ___ Go: Java: vTZOffsetName = oDate.withZoneSameInstant(oTZ).format(java.time.format.DateTimeFormatter.ofPattern("zzz", java.util.Locale.getDefault())) [e.g. oTZ = java.time.ZoneId.of("Europe/London")] [e.g. 'GMT', 'BST'] JavaScript: vTZOffsetName = oDate.toLocaleString("en", {timeZone:vTZ, day:"2-digit", timeZoneName:"long"}).slice(4) [e.g. for timeZone 'Europe/London': 'Greenwich Mean Time' / 'British Summer Time'] Kotlin: vTZOffsetName = oDate.withZoneSameInstant(oTZ).format(java.time.format.DateTimeFormatter.ofPattern("zzz", java.util.Locale.getDefault())) [e.g. oTZ = java.time.ZoneId.of("Europe/London")] [e.g. 'GMT', 'BST'] PHP: $oDate->setTimezone(timezone_open($vTZ))->format("T") [e.g. $vTZ = "Europe/London"] [e.g. 'GMT', 'BST'] Python: vTZOffsetName = oTZ.tzname(oDate) [e.g. oTZ = zoneinfo.ZoneInfo("Europe/London")] [e.g. 'GMT', 'BST'] R: Ruby: ___ [can use (UTC date): vTZDefault = ENV["TZ"]; ENV["TZ"] = vTZ; vOffsetName = oDate.getlocal.zone; ENV["TZ"] = vTZDefault] [e.g. vTZ = "Europe/London"] Rust: Scala: vTZOffsetName = oDate.withZoneSameInstant(oTZ).format(java.time.format.DateTimeFormatter.ofPattern("zzz", java.util.Locale.getDefault())) [e.g. oTZ = java.time.ZoneId.of("Europe/London")] [e.g. 'GMT', 'BST'] SQL (MySQL): ___ SQL (PostgreSQL): ___ [can use (current offset): e.g. BEGIN; SET LOCAL timezone = 'Europe/London'; SELECT abbrev FROM pg_timezone_names WHERE name = current_setting('timezone'); COMMIT;] [e.g. 'GMT', 'BST'] [note: the time zone is set only within the transaction] SQL (SQLite): ___ Swift: UFL: DateGetTZOffsetNameLocal [get the local (session/system) time zone offset name, in force at the time, for a date object][e.g. 'Greenwich Mean Time', 'British Summer Time', 'GMT', 'BST'][e.g. 'Eastern Standard Time', 'Eastern Daylight Time', 'EST', EDT'][note: the name can vary depending on whether or not DST is in force][e.g. convert a date object to a local date object, then get the value] AutoHotkey: ___ C++: C#: Crystal: oDate.to_local.zone.name [e.g. 'GMT', 'BST'] Excel: ___ Excel VBA: ___ Go: Java: vTZOffsetName = oDate.withZoneSameInstant(oTZ).format(java.time.format.DateTimeFormatter.ofPattern("zzz", java.util.Locale.getDefault())) [beforehand: oTZ = java.time.ZoneId.systemDefault()] [e.g. 'GMT', 'BST'] [e.g. oDate = java.time.ZonedDateTime.now()] JavaScript: vTZOffsetName = oDate.toLocaleString("en", {day:"2-digit", timeZoneName:"long"}).slice(4) Kotlin: vTZOffsetName = oDate.withZoneSameInstant(oTZ).format(java.time.format.DateTimeFormatter.ofPattern("zzz", java.util.Locale.getDefault())) [beforehand: oTZ = java.time.ZoneId.systemDefault()] [e.g. 'GMT', 'BST'] [e.g. oDate = java.time.ZonedDateTime.now()] PHP: $oDate->setTimezone(timezone_open(date_default_timezone_get()))->format("T") [e.g. 'GMT', 'BST'] Python: ___ [can use (current offset): vTZOffsetName = time.localtime().tm_zone] [also (returns a tuple, non-DST, DST): time.tzname] [e.g. 'GMT', 'BST'] [e.g. Windows: 'GMT Standard Time', 'GMT Daylight Time'] [can use (to return an offset name, although '%Z' should be avoided as it's OS-specific): oDate.astimezone().strftime("%Z")] R: Ruby: oDate.zone [also (UTC date): oDate.getlocal.zone] [also (date in non-local time zone): oDate.getutc.getlocal.zone] Rust: Scala: vTZOffsetName = oDate.withZoneSameInstant(oTZ).format(java.time.format.DateTimeFormatter.ofPattern("zzz", java.util.Locale.getDefault())) [beforehand: oTZ = java.time.ZoneId.systemDefault()] [e.g. 'GMT', 'BST'] [e.g. oDate = java.time.ZonedDateTime.now()] SQL (MySQL): ___ SQL (PostgreSQL): ___ [can use (current offset): abbrev FROM pg_timezone_names WHERE name = current_setting('timezone')] [e.g. 'GMT', 'BST'] SQL (SQLite): ___ Swift: UFL: DateGetTZOffsetName [for a date object, get the time zone offset name for the date and time zone][e.g. 'Greenwich Mean Time', 'British Summer Time', 'GMT', 'BST'][e.g. 'Eastern Standard Time', 'Eastern Daylight Time', 'EST', EDT'][note: the name can vary depending on whether or not DST is in force] AutoHotkey: ___ C++: C#: Crystal: oDate.zone.name [e.g. 'GMT', 'BST'] Excel: ___ Excel VBA: ___ Go: Java: vTZOffsetName = oDate.format(java.time.format.DateTimeFormatter.ofPattern("zzz", java.util.Locale.getDefault())) [e.g. 'GMT', 'BST'] [note: Locale.getDefault() has no effect on what time zone is used] JavaScript: ___ Kotlin: vTZOffsetName = oDate.format(java.time.format.DateTimeFormatter.ofPattern("zzz", java.util.Locale.getDefault())) [e.g. 'GMT', 'BST'] [note: Locale.getDefault() has no effect on what time zone is used] PHP: $oDate->format("T") [e.g. 'GMT', 'BST'] Python: ___ [WARNING: 'If neither system data nor tzdata are available, all calls to ZoneInfo will raise ZoneInfoNotFoundError.'] R: Ruby: oDate.zone [e.g. 'GMT', 'BST'] Rust: Scala: vTZOffsetName = oDate.format(java.time.format.DateTimeFormatter.ofPattern("zzz", java.util.Locale.getDefault())) [e.g. 'GMT', 'BST'] [note: Locale.getDefault() has no effect on what time zone is used] SQL (MySQL): ___ SQL (PostgreSQL): ___ SQL (SQLite): ___ Swift:
If using an online programming language compiler/interpreter, it may report 'UTC', and not your local time zone. It can be best to think of dates as a UTC time and a location. To display a date in a time zone, you query: for this UTC time, at this location, what was the time zone offset, and was DST (daylight saving time) in force. Time zones can be thought of as having 4 names. A location, a general name, a non-DST name, a DST name. E.g. Europe/London, 'British Time' (BT), Greenwich Mean Time (GMT) (UTC), British Summer Time (BST) (UTC+1). E.g. America/New_York, Eastern Time (ET), Eastern Standard Time (EST) (UTC-5), Eastern Daylight Time (EDT) (UTC-4). E.g. America/Chicago, Central Time (CT), Central Standard Time (CST) (UTC-6), Central Daylight Time (CDT) (UTC-5). E.g. America/Denver, Mountain Time (MT), Mountain Standard Time (MST) (UTC-7), Mountain Daylight Time (MDT) (UTC-6). E.g. America/Los_Angeles, Pacific Time (PT), Pacific Standard Time (PST) (UTC-8), Pacific Daylight Time (PDT) (UTC-7). Phoenix, Arizona uses Mountain Standard Time (MST) (UTC-7) all year round, it doesn't observe DST. The UK uses Greenwich Mean Time (GMT) (UTC) for 5 months of the year (late Oct-late Mar). The UK uses British Summer Time (BST) (UTC+1) for 7 months of the year (late Mar-late Oct). In US time zones, 'S' stands for 'Standard', i.e. non-DST. In UK time zones, 'S' stands for 'Summer', i.e. DST. When using time zone functionality... Some time zone information may be specific to the operating system (e.g. Windows-specific / Linux-specific).
Using toLocaleString() to format dates as 'yyyy-MM-dd HH:mm:ss' or 'yyyy-MM-ddTHH:mm:ss.000Z' It can be useful to format a date like so: 'yyyy-MM-dd HH:mm:ss'. E.g. '2006-05-04 03:02:01'. Such dates are easily sorted, and are well understood internationally. The following examples, ISO and locale, give values where the digits are in the same order: > ISO string: These give a date in the UTC time zone: E.g. new Date().toISOString() E.g. new Date(Date.UTC(2006, 5-1, 4, 3, 2, 1)).toISOString() E.g. '2006-05-04T03:02:01.000Z'. > locale string: These give a date in the specified time zone (or the local time zone if 'timezone' is omitted): E.g. new Date().toLocaleString("en-CA-u-hc-h23", {weekday:"short", year:"numeric", month:"2-digit", day:"2-digit", hour:"2-digit", minute:"2-digit", second:"2-digit", timeZoneName:"longOffset"}) E.g. new Date(Date.UTC(2006, 5-1, 4, 3, 2, 1)).toLocaleString("en-CA-u-hc-h23", {weekday:"short", year:"numeric", month:"2-digit", day:"2-digit", hour:"2-digit", minute:"2-digit", second:"2-digit", timeZoneName:"longOffset"}) E.g. new Date(Date.UTC(2006, 5-1, 4, 3, 2, 1)).toLocaleString("en-CA-u-hc-h23", {timezone:"Europe/London", weekday:"short", year:"numeric", month:"2-digit", day:"2-digit", hour:"2-digit", minute:"2-digit", second:"2-digit", timeZoneName:"longOffset"}) E.g. 'Thu, 2006-05-04, 04:02:01 GMT+01:00'. Note: there is no guarantee that the format won't change in future. E.g. the Canadian format was changed, then changed back. > various locale strings for comparison: E.g. new Date(Date.UTC(2006, 5-1, 4, 3, 2, 1)).toLocaleString("en-GB", {timezone:"Europe/London", weekday:"short", year:"numeric", month:"2-digit", day:"2-digit", hour:"2-digit", minute:"2-digit", second:"2-digit", timeZoneName:"longOffset"}) E.g. new Date(Date.UTC(2006, 5-1, 4, 3, 2, 1)).toLocaleString("en-US-u-hc-h23", {timezone:"Europe/London", weekday:"short", year:"numeric", month:"2-digit", day:"2-digit", hour:"2-digit", minute:"2-digit", second:"2-digit", timeZoneName:"longOffset"}) E.g. new Date(Date.UTC(2006, 5-1, 4, 3, 2, 1)).toLocaleString("en-CA-u-hc-h23", {timezone:"Europe/London", weekday:"short", year:"numeric", month:"2-digit", day:"2-digit", hour:"2-digit", minute:"2-digit", second:"2-digit", timeZoneName:"longOffset"}) E.g. new Date(Date.UTC(2006, 5-1, 4, 3, 2, 1)).toLocaleString("en-US", {timezone:"Europe/London", weekday:"short", year:"numeric", month:"2-digit", day:"2-digit", hour:"2-digit", minute:"2-digit", second:"2-digit", timeZoneName:"longOffset"}) E.g. new Date(Date.UTC(2006, 5-1, 4, 3, 2, 1)).toLocaleString("en-CA", {timezone:"Europe/London", weekday:"short", year:"numeric", month:"2-digit", day:"2-digit", hour:"2-digit", minute:"2-digit", second:"2-digit", timeZoneName:"longOffset"}) E.g. 'Thu, 04/05/2006, 04:02:01 GMT+01:00'. 'en-GB' (using dd/MM/yyyy). E.g. 'Thu, 05/04/2006, 04:02:01 GMT+01:00'. 'en-US-u-hc-h23' (using dd/MM/yyyy). E.g. 'Thu, 2006-05-04, 04:02:01 GMT+01:00'. 'en-CA-u-hc-h23'. E.g. 'Thu, 05/04/2006, 04:02:01 AM GMT+01:00'. 'en-US' (using MM/dd/yyyy). E.g. 'Thu, 2006-05-04, 04:02:01 a.m. GMT+01:00'. 'en-CA'. > Background: 'en', 'en-CA' and 'en-CA-u-hc-h23' are examples of BCP 47 language tags. I was looking for a 'default' BCP 47 tag, to use with toLocaleString(), that would convert dates to ISO format, and perhaps a variant that used English weekday/month names. Unfortunately, it appears that none currently exists (Dec 2023). The closest I could find were: af, lt, sv, en-CA. Afrikaans, Lithuanian, Swedish, and English (Canada) respectively. Unfortunately, although en-CA used ISO dates, it used 12-hour time, however, this could be fixed by appending '-u-hc-h23', to give: 'en-CA-u-hc-h23'. Note: 'hc' stands for hour cycle. Note: 'h23' indicates hours 0-23. Note: 'h23', not 'h24'. > Links: Intl.DateTimeFormat() constructor - JavaScript | MDN Intl.Locale - JavaScript | MDN Intl - JavaScript | MDN Intl.Locale.prototype.hourCycle - JavaScript | MDN IETF language tag - Wikipedia
Preferred algorithm for adding/subtracting years/months My preferred algorithm for adding/subtracting years/months, works as follows: Adding/subtracting months, always round down if the date doesn't exist: E.g. Oct 31 + 1 month = 'Nov 31', which doesn't exist, so round down to Nov 30. E.g. Dec 31 - 1 month = 'Nov 31', which doesn't exist, so round down to Nov 30. Adding/subtracting years, always round down if the date doesn't exist: E.g. Feb 29 + 1 year = 'Feb 29', which doesn't exist, so round down to Feb 28. E.g. Feb 29 - 1 year = 'Feb 29', which doesn't exist, so round down to Feb 28. One benefit of this approach is that you know which month you will end up in, it's simple: If you are in January, and you 'add one month', you will end up in February. If you are in February, and you 'subtract one month', you will end up in January. This algorithm is equivalent to the following: ● Microsoft Excel's EDATE function. ● Microsoft Excel VBA's DateAdd function (with 'm'/'yyyy' options). ● Microsoft Windows's SysDateTimePick32 controls. ● SQLite's add months/years 'floor' mode. This algorithm differs from: ● JavaScript's setUTCMonth/setMonth methods. Preferred algorithm for counting months between 2 dates My preferred algorithm for counting the complete months between 2 dates, works as follows: E.g. 2006-05-04 versus 2006-03-14. At first glance, 2006-05 v. 2006-03, the difference is 2 months. Now check the rest of the date (D/H/M/S/milliseconds). The days of the month: 4 v. 14, 4 < 14, so subtract 1 from the total month count. The result is a difference of 1 month. I.e. 14 March to 4 May, is 2 months minus 10 days, hence 1 complete month. E.g. 2006-05-14 versus 2006-03-04. At first glance, 2006-05 v. 2006-03, the difference is 2 months. Now check the rest of the date (D/H/M/S/milliseconds). The days of the month: 4 v. 14, 14 > 4, so leave the month count unchanged. The result is a difference of 2 months. I.e. 4 March to 14 May, is 2 months and 10 days, hence 2 complete months. In general: Compare the year and month values to get a month count. Then compare the rest of the date, and either subtract a month, or leave the value unchanged.
Sometimes 'weekday' means Mon/Tue/Wed/Thu/Fri but not Sat/Sun. Sometimes 'weekday' means any day of the week (Mon/Tue/Wed/Thu/Fri/Sat/Sun).
24-hour time, translates to 12-hour time like so: 0 -> 12 AM 1-11 -> 1-11 AM 12 -> 12 PM 13-23 -> 1-11 PM Note: 1-11 *AM*, is followed by 12 *PM*. Note: 1-11 *PM*, is followed by 12 *AM*.
Using the Gregorian calendar: To determine whether a year is a leap year or not: 4Y AND (100N OR 400Y): Where 4Y means divisible by 4. Where 100N means not divisible by 100. Where 400Y means divisible by 400. To be a leap year, it must pass 4Y, and pass either 100N or 400Y. Some example years: 1900 4Y 100Y 400N: passes 4Y, fails 100N, fails 400Y, NOT leap. 2000 4Y 100Y 400Y: passes 4Y, fails 100N, but passes 400Y, IS leap. 2001 4N 100N 400N: fails 4Y, NOT leap. 2004 4Y 100Y 400N: passes 4Y, passes 100N, IS leap. Century examples: Are leap: 1600, 2000, 2400. Aren't leap: 1700, 1800, 1900, 2100, 2200, 2300.
Combining dates and milliseconds Beware when combining bits of dates that were obtained at different times. Problem: Let's say the time is noon. 12:00:00. At 12:00:00.950, you get the time (12:00:00). At 12:00:00.970, you get the milliseconds (970). That gives: 12:00:00.970. = At 12:00:00.990, you get the time (12:00:00). At 12:00:01.010, you get the milliseconds (010). That gives: 12:00:00.010. You now have 2 'dates' that are out of sequence. Solution: Let's say the time is noon. 12:00:00. At 12:00:00.950, you get the time (12:00:00). At 12:00:00.970, you get the milliseconds (970). At 12:00:00.990, you get the time (12:00:00). That gives: 12:00:00.970. The time values matched (both 12:00:00), so we know the combined time is reliable. = At 12:00:01.010, you get the milliseconds (010). At 12:00:01.030, you get the time (12:00:01). The current time (12:00:01) and the previous time (12:00:00) don't match, so try again: = At 12:00:01.050, you get the milliseconds (050). At 12:00:01.070, you get the time (12:00:01). That gives: 12:00:01.050. The time values matched (both 12:00:01), so we know the combined time is reliable.
Note: 1 second = 1000 milliseconds 1 second = 1000000 microseconds 1 second = 10000000 100-nanosecond intervals (see FILETIME) 1 second = 1000000000 nanoseconds 1 millisecond = 1000 microseconds 1 millisecond = 1000000 nanoseconds 1 microsecond = 1000 nanoseconds 1 day = 24 hours = 1440 minutes = 86400 sec = 86400000 msec [note: 1440: 24*60=12*120=144*10] 1/86400 day = 1/3600 hour = 1/60 minute = 1 sec = 1000 msec 1/86400000 day = 1/3600000 hour = 1/60000 minute = 1/1000 sec = 1 msec Some common date systems: Unix (seconds since 1970-01-01 00:00:00) Excel (days since 1899-12-30 00:00:00) FILETIME (100-nanosecond intervals since 1601-01-01 00:00:00) ticks (.NET) (100-nanosecond intervals since 0001-01-01 00:00:00) Julian days (days since -4713-11-24 12:00:00) Listed below, the equivalents of various years in different date systems: Unix (seconds since 1970-01-01 00:00:00): note: sometimes *milliseconds* since 1970 is used, in which case the number is longer by 3 digits
e.g. JavaScript: console.log(Math.floor(new Date(Date.UTC(2000, 1-1, 1)).valueOf()/1000)); 1970 0 1980 315532800 1990 631152000 2000 946684800 2010 1262304000 2020 1577836800 2030 1893456000 2040 2208988800 2050 2524608000 2060 2840140800 2070 3155760000 2080 3471292800 2090 3786912000 2100 4102444800
Excel (days since 1899-12-30 00:00:00): note: Excel incorrectly assumes that the year 1900 is a leap year - Microsoft 365 Apps | Microsoft Learn Microsoft Excel incorrectly assumes that the year 1900 is a leap year. This article explains why the year 1900 is treated as a leap year, and outlines the behaviors that may occur if this specific issue is corrected. ... When Lotus 1-2-3 was first released, the program assumed that the year 1900 was a leap year, even though it actually was not a leap year. This made it easier for the program to handle leap years and caused no harm to almost all date calculations in Lotus 1-2-3. ... The WEEKDAY function returns incorrect values for dates before March 1, 1900. Because most users do not use dates before March 1, 1900, this problem is rare.
WARNING: the Excel VBA/Excel sheets numbering systems disagree for 1899-12-30, 1899-12-31, and Jan/Feb 1990 WARNING: the Excel VBA assumes that a non-existent day, exists: 1900-02-29 e.g. Excel VBA: Debug.Print DateSerial(2000, 1, 1) * 1 e.g. Excel sheets: =DATE(2000,1,1) 100 -657434 (Excel VBA: first available date) 500 -511337 1000 -328716 1500 -146095 1600 -109571 1601 -109205 1700 -73046 1800 -36522 1899-12-30 (Excel VBA: 0) (Excel sheets: -1) (Excel sheets: first available date) 1899-12-31 (Excel VBA: 1) (Excel sheets: 0) 1900-01-01 (Excel VBA: 2) (Excel sheets: 1) 1900-02-28 (Excel VBA: 60) (Excel sheets: 59) 1900-02-29 (Excel VBA: N/A) (Excel sheets: 60) (*non-existent day*) 1900-03-01 61 1901-01-01 367 1910 3654 1920 7306 1930 10959 1940 14611 1950 18264 1960 21916 1970 25569 1980 29221 1990 32874 2000 36526 2010 40179 2020 43831 2030 47484 2040 51136 2050 54789 2060 58441 2070 62094 2080 65746 2090 69399 2100 73051
FILETIME (100-nanosecond intervals since 1601-01-01 00:00:00):
e.g. C#: Console.WriteLine(new DateTime(2000, 1, 1).Ticks - 504911232000000000); 1601 0 1700 31241376000000000 1800 62798112000000000 1900 94354848000000000 1910 97510176000000000 1920 100665504000000000 1930 103821696000000000 1940 106977024000000000 1950 110133216000000000 1960 113288544000000000 1970 116444736000000000 1980 119600064000000000 1990 122756256000000000 2000 125911584000000000 2010 129067776000000000 2020 132223104000000000 2030 135379296000000000 2040 138534624000000000 2050 141690816000000000 2060 144846144000000000 2070 148002336000000000 2080 151157664000000000 2090 154313856000000000 2100 157469184000000000
ticks (.NET) (100-nanosecond intervals since 0001-01-01 00:00:00):
e.g. C#: Console.WriteLine(new DateTime(2000, 1, 1).Ticks); 1 0 100 31241376000000000 500 157469184000000000 1000 315253728000000000 1500 473038272000000000 1600 504595008000000000 1601 504911232000000000 [FILETIME + 504911232000000000 = ticks] 1700 536152608000000000 1800 567709344000000000 1900 599266080000000000 1910 602421408000000000 1920 605576736000000000 1930 608732928000000000 1940 611888256000000000 1950 615044448000000000 1960 618199776000000000 1970 621355968000000000 1980 624511296000000000 1990 627667488000000000 2000 630822816000000000 2010 633979008000000000 2020 637134336000000000 2030 640290528000000000 2040 643445856000000000 2050 646602048000000000 2060 649757376000000000 2070 652913568000000000 2080 656068896000000000 2090 659225088000000000 2100 662380416000000000
Julian days (days since -4713-11-24 12:00:00):
e.g. SQLite: SELECT julianday('2000-01-01');
-4713-11-24 12:00:00 0
-4712 37.5
-4000 260089.5
-3000 625332.5
-2000 990574.5
-1000 1355817.5
-1 1720694.5
0 1721059.5
1 1721425.5
100 1757584.5
500 1903681.5
1000 2086302.5
1500 2268923.5
1600 2305447.5
1601 2305813.5
1700 2341972.5
1800 2378496.5
1900 2415020.5
1910 2418672.5
1920 2422324.5
1930 2425977.5
1940 2429629.5
1950 2433282.5
1960 2436934.5
1970 2440587.5
1980 2444239.5
1990 2447892.5
2000 2451544.5
2010 2455197.5
2020 2458849.5
2030 2462502.5
2040 2466154.5
2050 2469807.5
2060 2473459.5
2070 2477112.5
2080 2480764.5
2090 2484417.5
2100 2488069.5
Year/Birth Year To Age If a person was born in the year Y1, then in the year Y2... They are age Y2-Y1-1 before their birthday. They are age Y2-Y1 after their birthday. During a person's birth year... They are age -1 before their birthday. They are age 0 after their birthday. Age/Year To Birth Year If a person is N years old in the year Y... If they haven't had their birthday yet that year, they were born in the year Y-N-1. If they've had their birthday that year, they were born in the year Y-N. Age/Birth Year To Year If a person was born in the year Y, and is N years old... They will be N years old at the end of the year Y+N. They will be N years old at the start of the year Y+N+1.
If an Android app written in Kotlin uses the java.time class... Some older mobile phones cannot run the app. A workaround is to use code lines such as: '@android.annotation.TargetApi(26)' These effectively 'comment out' functions when used on older mobile phones. See also re. using Java data classes in Kotlin: android - Creating Date objects in Kotlin for API level less than or equal to 16 - Stack Overflow 'date-time classes such as java.util.Date, java.util.Calendar, & java.text.SimpleDateFormat are now legacy, supplanted by the java.time classes'
14 Year Types If you define a year by the first weekday of the year, and by whether it is a common year or a leap year, that gives 14 possible year types. In the lists below, for 1900-2099, years are grouped by the weekdays on 1 Jan and 1 Mar: E.g. 1900: 1 Jan was a Monday, 1 Mar was a Thursday. 20th Century: Common years: MonThu: 1900,1906,1917,1923,1934,1945,1951,1962,1973,1979,1990 TueFri: 1901,1907,1918,1929,1935,1946,1957,1963,1974,1985,1991 WedSat: 1902,1913,1919,1930,1941,1947,1958,1969,1975,1986,1997 ThuSun: 1903,1914,1925,1931,1942,1953,1959,1970,1981,1987,1998 FriMon: 1909,1915,1926,1937,1943,1954,1965,1971,1982,1993,1999 SatTue: 1910,1921,1927,1938,1949,1955,1966,1977,1983,1994 SunWed: 1905,1911,1922,1933,1939,1950,1961,1967,1978,1989,1995 Leap years: MonFri: 1912,1940,1968,1996 TueSat: 1924,1952,1980 WedSun: 1908,1936,1964,1992 ThuMon: 1920,1948,1976 FriTue: 1904,1932,1960,1988 SatWed: 1916,1944,1972 SunThu: 1928,1956,1984 21st Century: Common years: MonThu: 2001,2007,2018,2029,2035,2046,2057,2063,2074,2085,2091 TueFri: 2002,2013,2019,2030,2041,2047,2058,2069,2075,2086,2097 WedSat: 2003,2014,2025,2031,2042,2053,2059,2070,2081,2087,2098 ThuSun: 2009,2015,2026,2037,2043,2054,2065,2071,2082,2093,2099 FriMon: 2010,2021,2027,2038,2049,2055,2066,2077,2083,2094 SatTue: 2005,2011,2022,2033,2039,2050,2061,2067,2078,2089,2095 SunWed: 2006,2017,2023,2034,2045,2051,2062,2073,2079,2090 Leap years: MonFri: 2024,2052,2080 TueSat: 2008,2036,2064,2092 WedSun: 2020,2048,2076 ThuMon: 2004,2032,2060,2088 FriTue: 2016,2044,2072 SatWed: 2000,2028,2056,2084 SunThu: 2012,2040,2068,2096 Note: 1903-1910 inclusive, each of the 8 years starts with a different weekday, except for Friday which appears twice Note: 2003-2010 inclusive, each of the 8 years starts with a different weekday, except for Thursday which appears twice Reusing Diaries You may have an old unused diary, or calendar. E.g. a diary from 1995: 1995 was a 'SunWed' year. That diary could be used in 2006/2017/2023/2034 etc. E.g. a diary from 1996. 1996 was a 'MonFri' year. A leap year. That diary could be used in 2024/2052/2080 etc. If a diary is for a leap year, it can take longer before a matching year arises. If you're impatient, one possibility is to use a diary where the first 2 months of the diary don't match the current year, but where the last 10 months do match. E.g. a diary from 1996. 1996 was a 'MonFri' year. You could use that diary during a 'TueFri' year. That diary could be used in 2002/2013/2019/2030/2041 etc. The weekdays for a 'MonFri' diary and a 'TueFri' year will differ during January and February. Also, a 'MonFri' diary will have a 29 Feb, while a 'TueFri' year won't. But a 'MonFri' diary and a 'TueFri' year will have the same structure for the months March to December. Note: bank holidays etc will typically be consistent. However, the date of Easter, and dates based on Easter, probably won't match. Note: Easter Sunday possible date range (Gregorian calendar): 22 March to 25 April inclusive. Some dates based on Easter: Good Friday BH, Easter Sunday, Easter Monday BH. Shrove Tuesday (47 days before Easter Sunday). Ash Wednesday (46 days before Easter Sunday). Mother's Day (UK) (21 days before Easter Sunday). Note: Mother's Day (UK) possible date range: 1 March to 4 April inclusive. Note: Mother's Day (US) possible date range: 8 May to 14 May inclusive. (Second Sunday in May, not based on Easter.) Link (compare key dates in different years): UK/US holidays - Bazzle Simple Week Numbers One simple way of defining weeks (equivalent to 'WEEKNUM(A1,2)' in Excel): Weeks start on Monday. Week 1 is the week containing 1 Jan. The first 'week' of the year may not have 7 days. The last 'week' of the year may not have 7 days. E.g. week numbers for the last day of the year (i.e. the number of 'weeks' in the year): Common years: MonThu: 2001 53 [52 complete weeks, 1 day] TueFri: 2002 53 [6 days, 51 complete weeks, 2 days] WedSat: 2003 53 [5 days, 51 complete weeks, 3 days] ThuSun: 2009 53 [4 days, 51 complete weeks, 4 days] FriMon: 2010 53 [3 days, 51 complete weeks, 5 days] SatTue: 2005 53 [2 days, 51 complete weeks, 6 days] SunWed: 2006 53 [1 day, 52 complete weeks] Leap years: MonFri: 2024 53 [52 complete weeks, 2 days] TueSat: 2008 53 [6 days, 51 complete weeks, 3 days] WedSun: 2020 53 [5 days, 51 complete weeks, 4 days] ThuMon: 2004 53 [4 days, 51 complete weeks, 5 days] FriTue: 2016 53 [3 days, 51 complete weeks, 6 days] SatWed: 2000 53 [2 days, 52 complete weeks] SunThu: 2012 54 [1 day, 52 complete weeks, 1 day] ISO Week Numbers The ISO week definition: Weeks start on Monday. Week 1 is the week containing 4 Jan. (The week containing the first Thursday of the year.) All weeks have 7 days. Week numbers are of the form 'yyyyMM'. Where 'yyyy' may not match the year number in the first/last ISO weeks of the year. E.g. ISO week numbers for the first and last day of the year: Common years: MonThu: 2001 200101-200201 [spans 53 weeks] [penultimate week: 200152] TueFri: 2002 200201-200301 [spans 53 weeks] [penultimate week: 200252] WedSat: 2003 200301-200401 [spans 53 weeks] [penultimate week: 200352] ThuSun: 2009 200901-200953 [spans 53 weeks] FriMon: 2010 200953-201052 [spans 53 weeks] SatTue: 2005 200453-200552 [spans 53 weeks] SunWed: 2006 200552-200652 [spans 53 weeks] Leap years: MonFri: 2024 202401-202501 [spans 53 weeks] [penultimate week: 202452] TueSat: 2008 200801-200901 [spans 53 weeks] [penultimate week: 200852] WedSun: 2020 202001-202053 [spans 53 weeks] ThuMon: 2004 200401-200453 [spans 53 weeks] FriTue: 2016 201553-201652 [spans 53 weeks] SatWed: 2000 199952-200052 [spans 53 weeks] SunThu: 2012 201152-201301 [spans 54 weeks] [penultimate week: 201252] Note: the week spans are essentially the same as for 'WEEKNUM(A1,2)'. This is because both systems define weeks as starting on a Monday. JavaScript code for generating year lists
//code example (JavaScript):
//get weekdays for 1 Jan/1 Mar of each year e.g. 'MonThu':
oObj = {};
for (let i=1900; i<=1999; i++)
{
vYear = i;
//vYear += 100; //for the next century
oDate1 = new Date(Date.UTC(vYear, 1-1));
oDate2 = new Date(Date.UTC(vYear, 3-1));
vWDayName1 = oDate1.toLocaleString("en", {weekday:"short"});
vWDayName2 = oDate2.toLocaleString("en", {weekday:"short"});
vWDayNames = vWDayName1 + vWDayName2;
if (vWDayNames in oObj)
oObj[vWDayNames] += "," + vYear;
else
oObj[vWDayNames] = "" + vYear;
}
console.log(oObj);
Python code for generating week number lists
#code example (Python):
#week numbers (equivalent to 'WEEKNUM(A1,2)' in Excel):
import datetime
oList = [2001,2002,2003,2009,2010,2005,2006,2024,2008,2020,2004,2016,2000,2012]
vOutput = ""
for vYear in oList:
oDate = datetime.datetime(vYear, 12, 31)
vWeek = int(oDate.strftime("%W")) + (1 if datetime.date(oDate.year,1,1).weekday() else 0)
vOutput += str(vYear) + "\t" + str(vWeek) + "\r\n"
print(vOutput)
#code example (Python):
#ISO weeks:
import datetime
oList = [2001,2002,2003,2009,2010,2005,2006,2024,2008,2020,2004,2016,2000,2012]
vOutput = ""
for vYear in oList:
vIsoYWeek1 = datetime.datetime(vYear, 1, 1).strftime("%G%V") #first week of year
vIsoYWeek2 = datetime.datetime(vYear, 12, 24).strftime("%G%V") #penultimate week of year
vIsoYWeek3 = datetime.datetime(vYear, 12, 31).strftime("%G%V") #last week of year
vOutput += str(vYear) + "\t" + vIsoYWeek1 + "\t" + vIsoYWeek2 + "\t" + vIsoYWeek3 + "\r\n"
print(vOutput)
Since some important week number functionality is not available in Excel 2007 and earlier, we consider calculating week numbers from first principles. We will consider 2 definitions of week number. Excel: 'WEEKNUM(_, 2)' Weeks start on a Monday. The week containing January 1st is week 1. The first and last weeks of the year may have fewer than 7 days. Excel: 'ISOWEEKNUM' Weeks start on a Monday. The week containing January 4th is week 1. Every week has exactly 7 days. Days within Dec 29-31 may belong to the next year. Days within Jan 1-3 may belong to the previous year. ... This means some ISO weeks have days in 2 different years. > Weeks that start on Monday: A year that starts on Monday E.g.: Jan 1-Jan 7: Week 1 Jan 8-Jan 14: Week 2 Jan 15-Jan 21: Week 3 Etc. We can calculate date to week number like so: =INT((A1-DATE(YEAR(A1),1,1))/7)+1 Note: the DATE function creates a date from year/month/day values. I.e. 'date - Jan 1' gives us the number of elapsed days (through the year so far). Divide by 7, and truncate, to get the number of completed weeks. That gives us the nth week (0-based), i.e. the 1st week would be 0, the 2nd week would be 1. So add 1 to get the nth week (1-based), i.e. the 1st week would be 1, the 2nd week would be 2. > Weeks that start on Monday: In general: 'WEEKNUM(_, 2)' If a year starts on Tuesday. Week 1 is only 6 days long. As though by Jan 1, 1 day of the week has already elapsed. As though the year started on 31 Dec. ... If a year starts on Sunday. Week 1 is only 1 day long. As though by Jan 1, 6 days of the week have already elapsed. As though the year started on 26 Dec. Which gives us this table, where 'weekday' is the weekday on 1 Jan, where 'adjustment' is the number of days of week 1 that have 'elapsed' by 1 Jan:
weekday adjustment Mon 0 Tue 1 Wed 2 Thu 3 Fri 4 Sat 5 Sun 6
It so happens that: WEEKDAY(A1,3) gives exactly the values in 'adjustment'. E.g. if A1 contains a date whose weekday is Wednesday, WEEKDAY(A1,3) will return 2. Here's our formula that handles years starting on Monday: =INT((A1-DATE(YEAR(A1),1,1))/7)+1 Here's a formula that handles years starting on any day: =INT((A1-DATE(YEAR(A1),1,1)+WEEKDAY(DATE(YEAR(A1),1,1),3))/7)+1 Which is equivalent to: =WEEKNUM(A1, 2) I.e. we added '+WEEKDAY(DATE(YEAR(A1),1,1),3)' to generate the 'adjustment' value. > Weeks that start on Monday: In general: 'WEEKNUM(_, 2)': without using 'WEEKDAY(_, 3)' We took advantage of 'WEEKDAY(_, 3)'. But some versions of Excel lack this. Here's a summary: WEEKDAY(_, 1): Sunday = 1, Monday = 2, ... , Saturday = 7 WEEKDAY(_, 2): Monday = 1, Tuesday = 2, ... , Sunday = 7 WEEKDAY(_, 3): Monday = 0, Tuesday = 1, ... , Sunday = 6 Note: 'WEEKDAY(_)' is equivalent to 'WEEKDAY(_, 1)'. If we use 'WEEKDAY(_, 1)', we get a new table:
weekday x adjustment Mon 2 0 Tue 3 1 Wed 4 2 Thu 5 3 Fri 6 4 Sat 7 5 Sun 1 6 where: x is WEEKDAY(_) for 1st Jan
The numbers are simpler if we consider the weekday for 31st Dec.
weekday x adjustment Mon 1 0 Tue 2 1 Wed 3 2 Thu 4 3 Fri 5 4 Sat 6 5 Sun 7 6 where: x is WEEKDAY(_) for 31st Dec where: weekday is the weekday name for 1st Jan
So, WEEKDAY(_) for 31st Dec, gives us a value 1 larger than the required adjustment. Here's our formula that handles years starting on any day, that uses 'WEEKDAY(_, 3)': =INT((A1-DATE(YEAR(A1),1,1)+WEEKDAY(DATE(YEAR(A1),1,1),3))/7)+1 Here's a formula that handles years starting on any day, using 'WEEKDAY(_)': =INT(((A1-DATE(YEAR(A1)-1,12,31)+WEEKDAY(DATE(YEAR(A1)-1,12,31)))+5)/7) Let's explain the differences: In the first, it's '(date - 1st Jan + adjustment) / 7 + 1 Equivalent to: '(date - 1st Jan + adjustment + 7) / 7 In the second, it's '(date - 31st Dec - 1 + bigger adjustment - 1 + 7) / 7 Equivalent to: '(date - 31st Dec + bigger adjustment + 5) / 7 Note: the difference between date and 31st Dec, is 1 bigger than for 1 Jan, so we subtract 1. Note: the adjustment is 1 bigger than required, so we subtract 1. Note: we changed both dates to 31st Dec, it's simpler if both dates have the same value. (We could have kept the first date as 1st Jan.) > ISO weeks: ISOWEEKNUM In the 'WEEKDAY(_, 2)' example, the logic considered the 1st week of the year starting between 26 Dec and 1 Jan. For ISOWEEKNUM, we can instead consider the 1st week of the year starting between 29 Dec and 4 Jan. Here are our two formulas for 'WEEKDAY(_, 2)', with/without 'WEEKDAY(_,3)': =INT((A1-DATE(YEAR(A1),1,1)+WEEKDAY(DATE(YEAR(A1),1,1),3))/7)+1 =INT(((A1-DATE(YEAR(A1)-1,12,31)+WEEKDAY(DATE(YEAR(A1)-1,12,31)))+5)/7) Here are two formulas for ISOWEEKNUM, with/without 'WEEKDAY(_,3)': =INT((A1-DATE(YEAR(A1-WEEKDAY(A1-1)+4),1,4)+WEEKDAY(DATE(YEAR(A1-WEEKDAY(A1-1)+4),1,4),3))/7)+1 =INT((A1-DATE(YEAR(A1-WEEKDAY(A1-1)+4),1,3)+WEEKDAY(DATE(YEAR(A1-WEEKDAY(A1-1)+4),1,3))+5)/7) Note: YEAR(A1-WEEKDAY(A1-1)+4) gives the 'ISO year' for a date, the logic is explained lower down. Note: the 'WEEKDAY(_)' form for ISOWEEKNUM appears at excelribbon.tips.net. Note: 'DATE(YEAR(A1-WEEKDAY(A1-1)+4),1,4)' appears twice in the first formula. Note: 'DATE(YEAR(A1-WEEKDAY(A1-1)+4),1,3)' appears twice in the second formula. Note: WEEKDAY(A1) gives: Sunday = 1, Monday = 2, ... , Saturday = 7 Note: WEEKDAY(A1-1) gives: Monday = 1, Tuesday = 2, ... , Sunday = 7 Note: WEEKDAY(A1-1) is equivalent to WEEKDAY(A1, 2), and equivalent to INT(MOD(A1+5,7))+1 Note: WEEKDAY(A1-1) is a hack: use the weekday value for yesterday, to get a value for today where Monday is the smallest > ISO weeks: ISOWEEKNUM: calculating the ISO year YEAR(A1-WEEKDAY(A1-1)+4) gives the 'ISO year' for a date. '-WEEKDAY(_)' returns a number between -7 and -1 inclusive. So '-WEEKDAY(A1-1)+4' returns a number between -4 and 3 inclusive. This means that some dates in early January will return the previous year, and that some dates in late December will return the next year. A summary of ISO weeks that contain dates from 2 different years:
weekday details
Mon ('ISO years' match years) (week 1: 1 Jan-7 Jan)
Tue week 1 includes 31 Dec (week 1: 31 Dec-6 Jan)
Wed week 1 includes 30-31 Dec (week 1: 30 Dec-5 Jan)
Thu week 1 includes 29-31 Dec (week 1: 29 Dec-4 Jan)
Fri 1-3 Jan in previous ISO year (week 1: 4 Jan-10 Jan)
Sat 1-2 Jan in previous ISO year (week 1: 3 Jan-9 Jan)
Sun 1 Jan in previous ISO year (week 1: 2 Jan-8 Jan)
where: weekday is the weekday on 1st Jan
The date of Remembrance Sunday falls between the 8th and the 14th of November inclusive. There are two common definitions for the date of Remembrance Sunday: - the 2nd Sunday of November - the nearest Sunday on/around the 11th of November They are equivalent, but it is not immediately obvious why. 2nd Sunday of November The 1st Sunday of November occurs within 1st-7th November inclusive. Thus, the 2nd Sunday of November occurs within 8th-14th November inclusive, 7 days later. The 3rd Sunday, 15th-21st. The 4th Sunday, 22nd-28th. Nearest Sunday on/around 11 Nov Let's consider the nearest Sunday on/around the 4th of November. It will occur in the range 1st-7th of November inclusive. The 1st Sunday of November. Now let's consider the nearest Sunday on/around the 5th of November. It will occur in the range 2nd-8th of November inclusive. It will be the 1st or 2nd Sunday of November. Now let's consider the nearest Sunday on/around the 3rd of November. It will occur in the range 31st of October-6th of November inclusive. It will be the last Sunday of October, or 1st Sunday of November. Now let's consider the nearest Sunday on/around the 11th of November. It will occur in the range 8th-14th of November inclusive. The 2nd Sunday of November. The numbers 4/11/18/25 are 'special', they are the averages of 1-7/8-14/15-21/22-28 respectively. The nearest Sunday on/around those dates neatly fits such that they occur on the 1st/2nd/3rd/4th Sunday of November respectively. Thus, the nearest Sunday on/around 4 Nov is the 1st Sunday of November. And the nearest Sunday on/around 11 Nov is the 2nd Sunday of November. On/around 18 Nov, 3rd Sunday of November. On/around 25 Nov, 4th Sunday of November.
The only item currently marked as lower priority is DateGetEra (e.g. return AD). Another relatively low priority item is DateGetYDay. One use is to schedule something every n days.
note: this is the original outline on which this page was based note: further items were since added new date: UTC new date: local new date: any time zone new date: tick count (e.g. milliseconds since program started, milliseconds since PC switched on) date to string: UTC string date to string: local date string with time zone stated date to string: 14-digit string (yyyyMMddHHmmss) date to string: 6-member array (yyyy, MM, dd, HH, mm, ss) date to string: using similar formatting to AutoHotkey's FormatTime, and the Winapi's GetDateFormat/GetTimeFormat date to number: get week number (week containing January 1st is numbered week 1) date to number: get ISO week number (week containing the first Thursday of the year is numbered week 1) date add: days/hours/minutes/seconds/milliseconds date add: years/months (same algorithm as Microsoft SysDateTimePick32 controls) date diff: difference in days/hours/minutes/seconds/milliseconds date diff: difference in years/months (and handling tiebreaks) date is valid: validate string calculate key dates: Easter calculate key dates: nearest weekday to a specific date calculate key dates: nth weekday in range (e.g. 1st/2nd/3rd/4th/last Sunday of the month)