$dateFromString (aggregation)

On this page

Definition

Behavior
Format Specifiers
Examples

MongoDB 5.0 is end of life as of October 2024. This version of the documentation is no longer supported. To upgrade your 5.0 deployment, see the MongoDB 6.0 upgrade procedures.

Definition

$dateFromString

New in version 3.6.

Converts a date/time string to a date object.

The $dateFromString expression has the following syntax:

{ $dateFromString: {
     dateString: <dateStringExpression>,
     format: <formatStringExpression>,
     timezone: <tzExpression>,
     onError: <onErrorExpression>,
     onNull: <onNullExpression>
} }

The $dateFromString takes a document with the following fields:

Field	Description
`dateString`	The date/time string to convert to a date object. See Date for more information on date/time formats. If specifying the `timezone` option to the operator, do not include time zone information in the `dateString`.
`format`	Optional. The date format specification of the `dateString`. The `format` can be any expression that evaluates to a string literal, containing 0 or more format specifiers. For a list of specifiers available, see Format Specifiers. If unspecified, `$dateFromString` uses `"%Y-%m-%dT%H:%M:%S.%LZ"` as the default format but accepts a variety of formats and attempts to parse the `dateString` if possible.
`timezone`	Optional. The time zone to use to format the date. If the `dateString` argument is formatted like '2017-02-08T12:10:40.787Z', in which the 'Z' at the end indicates Zulu time (UTC time zone), you cannot specify the `timezone` argument. `<timezone>` allows for the following options and expressions that evaluate to them: an Olson Timezone Identifier, such as `"Europe/London"` or `"America/New_York"`, or a UTC offset in the form: `+/-[hh]:[mm]`, e.g. `"+04:45"`, or `+/-[hh][mm]`, e.g. `"-0530"`, or `+/-[hh]`, e.g. `"+03"`, or The strings `"Z"`, `"UTC"`, or `"GMT"` For more information on expressions, see Expressions.
`onError`	Optional. If `$dateFromString` encounters an error while parsing the given `dateString`, it outputs the result value of the provided `onError` expression. This result value can be of any type. If you do not specify `onError`, `$dateFromString` throws an error if it cannot parse `dateString`.
`onNull`	Optional. If the `dateString` provided to `$dateFromString` is `null` or missing, it outputs the result value of the provided `onNull` expression. This result value can be of any type. If you do not specify `onNull` and `dateString` is `null` or missing, then `$dateFromString` outputs `null`.

Tip

Behavior

Example

Results

{ $dateFromString: {
    dateString: "2017-02-08T12:10:40.787"
} }

ISODate("2017-02-08T12:10:40.787Z")

{ $dateFromString: {
     dateString: "2017-02-08T12:10:40.787",
     timezone: "America/New_York"
} }

ISODate("2017-02-08T17:10:40.787Z")

{ $dateFromString: {
     dateString: "2017-02-08"
} }

ISODate("2017-02-08T00:00:00Z")

{ $dateFromString: {
     dateString: "06-15-2018",
     format: "%m-%d-%Y"
} }

ISODate("2018-06-15T00:00:00Z")

{ $dateFromString: {
     dateString: "15-06-2018",
     format: "%d-%m-%Y"
} }

ISODate("2018-06-15T00:00:00Z")

{ $dateFromString: {
     dateString: "WED jan 31 12:05:28 +03:30 1996"
} }

ISODate("1996-01-31T08:35:28.000Z")

Format Specifiers

The following format specifiers are available for use in the <formatString>:

Specifiers	Description	Possible Values
`%d`	Day of Month (2 digits, zero padded)	`01`-`31`
`%G`	Year in ISO 8601 format	`0000`-`9999`
`%H`	Hour (2 digits, zero padded, 24-hour clock)	`00`-`23`
`%L`	Millisecond (3 digits, zero padded)	`000`-`999`
`%m`	Month (2 digits, zero padded)	`01`-`12`
`%M`	Minute (2 digits, zero padded)	`00`-`59`
`%S`	Second (2 digits, zero padded)	`00`-`60`
`%u`	Day of week number in ISO 8601 format (1-Monday, 7-Sunday)	`1`-`7`
`%V`	Week of Year in ISO 8601 format	`1`-`53`
`%Y`	Year (4 digits, zero padded)	`0000`-`9999`
`%z`	The timezone offset from UTC.	`+/-[hh][mm]`
`%Z`	The minutes offset from UTC as a number. For example, if the timezone offset (`+/-[hhmm]`) was `+0445`, the minutes offset is `+285`.	`+/-mmm`
`%%`	Percent Character as a Literal	`%`

Examples

Converting Dates

Consider a collection logmessages that contains the following documents with dates.

{ _id: 1, date: "2017-02-08T12:10:40.787", timezone: "America/New_York", message:  "Step 1: Started" },
{ _id: 2, date: "2017-02-08", timezone: "-05:00", message:  "Step 1: Ended" },
{ _id: 3, message:  " Step 1: Ended " },
{ _id: 4, date: "2017-02-09", timezone: "Europe/London", message: "Step 2: Started"},
{ _id: 5, date: "2017-02-09T03:35:02.055", timezone: "+0530", message: "Step 2: In Progress"}

The following aggregation uses $dateFromString to convert the date value to a date object:

db.logmessages.aggregate( [ {
   $project: {
      date: {
         $dateFromString: {
            dateString: '$date',
            timezone: 'America/New_York'
         }
      }
   }
} ] )

The above aggregation returns the following documents and converts each date field to the Eastern Time Zone:

{ "_id" : 1, "date" : ISODate("2017-02-08T17:10:40.787Z") }
{ "_id" : 2, "date" : ISODate("2017-02-08T05:00:00Z") }
{ "_id" : 3, "date" : null }
{ "_id" : 4, "date" : ISODate("2017-02-09T05:00:00Z") }
{ "_id" : 5, "date" : ISODate("2017-02-09T08:35:02.055Z") }

The timezone argument can also be provided through a document field instead of a hard coded argument. For example:

db.logmessages.aggregate( [ {
   $project: {
      date: {
         $dateFromString: {
            dateString: '$date',
            timezone: '$timezone'
         }
      }
   }
} ] )

The above aggregation returns the following documents and converts each date field to their respective UTC representations.

{ "_id" : 1, "date" : ISODate("2017-02-08T17:10:40.787Z") }
{ "_id" : 2, "date" : ISODate("2017-02-08T05:00:00Z") }
{ "_id" : 3, "date" : null }
{ "_id" : 4, "date" : ISODate("2017-02-09T00:00:00Z") }
{ "_id" : 5, "date" : ISODate("2017-02-08T22:05:02.055Z") }

`onError`

If your collection contains documents with unparsable date strings, $dateFromString throws an error unless you provide an aggregation expression to the optional onError parameter.

For example, given a collection dates with the following documents:

{ "_id" : 1, "date" : "2017-02-08T12:10:40.787", timezone: "America/New_York" },
{ "_id" : 2, "date" : "20177-02-09T03:35:02.055", timezone: "America/New_York" }

You can use the onError parameter to return the invalid date in its original string form:

db.dates.aggregate( [ {
   $project: {
      date: {
         $dateFromString: {
            dateString: '$date',
            timezone: '$timezone',
            onError: '$date'
         }
      }
   }
} ] )

This returns the following documents:

{ "_id" : 1, "date" : ISODate("2017-02-08T17:10:40.787Z") }
{ "_id" : 2, "date" : "20177-02-09T03:35:02.055" }

`onNull`

If your collection contains documents with null date strings, $dateFromString returns null unless you provide an aggregation expression to the optional onNull parameter.

For example, given a collection dates with the following documents:

{ "_id" : 1, "date" : "2017-02-08T12:10:40.787", timezone: "America/New_York" },
{ "_id" : 2, "date" : null, timezone: "America/New_York" }

You can use the onNull parameter to have $dateFromString return a date representing the unix epoch instead of null:

db.dates.aggregate( [ {
   $project: {
      date: {
         $dateFromString: {
            dateString: '$date',
            timezone: '$timezone',
            onNull: new Date(0)
         }
      }
   }
} ] )

This returns the following documents:

{ "_id" : 1, "date" : ISODate("2017-02-08T17:10:40.787Z") }
{ "_id" : 2, "date" : ISODate("1970-01-01T00:00:00Z") }

Back

$dateFromParts

$dateSubtract