Docs Menu
Docs Home
/
MongoDB Manual
/
Reference
/
Operators
/
Aggregation Pipeline Operators

$dateFromString (aggregation)

On this page

  • Definition
  • Behavior
  • Format Specifiers
  • Examples

Definition

$dateFromString

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.

Note

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.

Note

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:

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

See also:

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: "oct 20 2020"
} }
ISODate("2020-10-20T00:00:00.000Z")
{ $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
%b
Abbreviated month (3 letters)
jan, feb, mar, apr, may, jun, jul, aug, sep, oct, nov, dec
%B
Full month name
january-december
%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
%j
Day of year (3 digits, zero padded)
001-366
%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
%U
Week of year (2 digits, zero padded)
00-53
%V
Week of Year in ISO 8601 format
1-53
%w
Day of week as an integer (0-Sunday, 6-Saturday)
0-6
%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 (aggregation)