Docs Home → MongoDB Manual

$dateFromString (aggregation)

On this page

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:

FieldDescription
dateStringThe 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.
formatOptional. 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.
timezoneOptional. 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:
  • 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.
onErrorOptional. 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.
onNullOptional. 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

ExampleResults
 
{ $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>:

SpecifiersDescriptionPossible Values
%bAbbreviated month (3 letters)jan, feb, mar, apr, may, jun, jul, aug, sep, oct, nov, dec
%BFull month namejanuary-december
%dDay of month (2 digits, zero padded)01-31
%GYear in ISO 8601 format0000-9999
%HHour (2 digits, zero padded, 24-hour clock)00-23
%jDay of year (3 digits, zero padded)001-366
%LMillisecond (3 digits, zero padded)000-999
%mMonth (2 digits, zero padded)01-12
%MMinute (2 digits, zero padded)00-59
%SSecond (2 digits, zero padded)00-60
%uDay of week number in ISO 8601 format (1-Monday, 7-Sunday)1-7
%UWeek of year (2 digits, zero padded)00-53
%VWeek of Year in ISO 8601 format1-53
%wDay of week as an integer (0-Sunday, 6-Saturday)0-6
%YYear (4 digits, zero padded)0000-9999
%zThe timezone offset from UTC.+/-[hh][mm]
%ZThe 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") }