{"id":181,"date":"2015-11-18T00:35:19","date_gmt":"2015-11-18T00:35:19","guid":{"rendered":"http:\/\/zappysys.com\/blog\/?p=181"},"modified":"2025-10-17T12:52:58","modified_gmt":"2025-10-17T12:52:58","slug":"how-to-query-mongodb-by-date-or-isodate","status":"publish","type":"post","link":"https:\/\/zappysys.com\/blog\/how-to-query-mongodb-by-date-or-isodate\/","title":{"rendered":"How to query MongoDB by date or ISODate"},"content":{"rendered":"

Introduction<\/h2>\n

\"\"ZappySys provides high-performance drag-and-drop connectors for MongoDB Integration. In our previous post, we discussed how to query\/load MongoDB data (Insert, Update, Delete, Upsert)<\/a>.\u00a0In this post, you will see how to\u00a0query MongoDB by date<\/em>\u00a0(or ISODate) using the\u00a0SSIS MongoDB Source<\/a>.<\/span>\u00a0To see full details about possible query syntax,\u00a0see online help<\/a>.<\/p>\n

MongoDB doesn’t have SQL Like query language so sometime you may feel odd…But has native JSON-like Query language (Sorry, SQL Guys.. )<\/p>\n

No worry if you stumble upon how to query various things in MongoDB. It’s different but not too hard to learn.<\/p>\n

MongoDB\u00a0Query Example<\/h2>\n

Assume that you have the following Document stored in a MongoDB Collection. You want to query orders where the Order date is greater than a specific date. Here are some examples of MongoDB queries used by ZappySys SSIS MongoDB Source Connector<\/a>.\u00a0For more information about MongoDB Query, check the\u00a0MongoDB Query Syntax documentation<\/a>.<\/span> An important thing is that your attribute must be stored as ISODate (not string). See the JSON document below; notice how OrderDate is saved as ISODate(…). This will allow Query to use any standard comparison operator (e.g. =, >, <, \u00a0!= ) for date search.\u00a0 If your data is not stored as ISODate, refer to online blogs\/help on how to write update scripts to convert string dates to ISODate.<\/p>\n

{\r\n\t\"OrderID\" : 100,\r\n\t\"OrderDate\" : ISODate(\"2015-02-28T00:00:00Z\"),\r\n\t\"Country\" : \"USA\",\r\n\t\"Total\" : 5000\r\n}\r\n<\/pre>\n
 <\/p>\n
How to Query MongoDB ISODate Attribute<\/h2>\n
If you want to query the ISODate attribute, you may scratch your head about how to do it. There are two ways to do<\/p>\n
Using\u00a0SQL Query\u00a0syntax (Only available in ZappySys SSIS MongoDB Source<\/a>)<\/strong><\/p>\n
select * from Orders where OrderDate > 'DATE(2015-01-01)'<\/pre>\n
Using SSIS Variable in Query<\/strong>
\nYou can also use an SSIS Variable to make your query dynamic. Variable placeholder<\/a> supports datetime format specifiers too. E.g., for an ISO date with 3-digit millisecond precision, you may use yyyy-MM-ddTHH:mm:ss.fffZ format as below.<\/p>\n
select * from Orders where OrderDate < 'DATE({{User::MyDateVar,yyyy-MM-ddTHH:mm:ss.fffZ}})'<\/pre>\n
Using ISODate syntax (SSIS MongoDB Source – Native Query)<\/strong><\/p>\n
{Table: Orders }\r\n{\r\n\t\"OrderDate\" : { \"$lt\" : new ISODate(\"2015-01-02T00:16:15.184Z\")}\r\n}\r\n<\/pre>\n
Using $date syntax (SSIS MongoDB Source – Native Query)<\/strong><\/p>\n
{Table: Orders }\r\n{\r\n\t\"OrderDate\" : { \"$gt\" : { \"$date\" : \"2015-01-01\"}}\r\n}\r\n<\/pre>\n
Remember to suffix “Z” in your date format to indicate its UTC date<\/h2>\n
MongoDB stores datetime values in UTC (to confirm this, edit a Mongo document and you will see a Z at the end in the ISODate(….) value). When you use ZappySys MongoDB SQL Query Syntax like below, it may or may not perform\u00a0Automatic Date Conversion<\/strong>\u00a0from\u00a0Local to UTC<\/strong>.<\/span> If you append “Z” to a date\/time value, it’s treated as UTC, and no DateTime conversion occurs. Dates without a Z at the end are treated as Local Date\/Time, so they’re converted to UTC before being passed to MongoDB.<\/p>\n
If you use JSON Style Native Query Syntax, ZappySys does not perform any date conversion.<\/p>\n
Query-1<\/strong>: Date supplied as UTC (Suffixed with “Z”<\/strong>) – Date is passed as is to the Mongo engine<\/p>\n
select * from Orders where OrderDate > 'DATE({{User::MyDateVar,yyyy-MM-ddTHH:mm:ss.fffZ}})'\r\nselect * from Orders where OrderDate > 'DATE({{User::MyDateVar,yyyy-MM-ddZ}})'<\/pre>\n
Query-2<\/strong>: Date supplied as Local Time (No “Z”<\/strong> at the end) – Date is converted to UTC and then passed to the Mongo Engine<\/p>\n
select * from Orders where OrderDate > 'DATE({{User::MyDateVar,yyyy-MM-ddTHH:mm:ss.fff}})'\r\nselect * from Orders where OrderDate > 'DATE({{User::MyDateVar,yyyy-MM-dd}})'\r\n<\/pre>\n
 <\/p>\n
MongoDB Query using SSIS MongoDB Source<\/h2>\n
ZappySys SSIS MongoDB Source<\/a> supports querying nested Documents using JSON Path expressions (e.g., Extract order Items from Order Document using $.Items expression)<\/p>\n
Example MongoDB Document<\/p>\n
 <\/p>\n
{\r\n    \"_id\" : ObjectId(\"564bb3bea1e57e043363f1ea\"),\r\n    \"OrderID\" : 111,\r\n    \"OrderDate\" : ISODate(\"2022-01-01T00:00:00.000Z\"),\r\n    \"AuditInfo\" : {\r\n        \"User\" : \"bob\",\r\n        \"CDate\" : ISODate(\"2015-01-01T00:00:00.000Z\")\r\n    },\r\n    \"Items\" : [ \r\n        {\r\n            \"ProductID\" : 100,\r\n            \"Qty\" : 3\r\n        }, \r\n        {\r\n            \"ProductID\" : 101,\r\n            \"Qty\" : 2\r\n        }\r\n    ]\r\n}<\/pre>\n
MongoDB Source –\u00a0SQL style\u00a0MongoDB Query (filter by date)<\/strong><\/p>\n
Use the DATE function to convert a string value to an ISODate<\/p>\n
\"\"<\/a>
SSIS MongoDB Source – MongoDB SQL Query – Filter by date (Query ISODate)<\/p><\/div>\n
MongoDB Source – Native MongoDB Query\u00a0 (filter by date)<\/strong><\/p>\n
\"\"<\/a>
Query MongoDB by Date Attribute (Query ISODate) using SSIS MongoDB Source<\/p><\/div>\n
Dynamic MongoDB Query using Placeholders<\/h2>\n
MongoDB Source<\/a> in SSIS PowerPack supports placeholders to make queries dynamic. You can use Query Placeholders like below (See Insert Placeholder menu)<\/p>\n
\"\"<\/a>
Dynamic MongoDB Query – Use SSIS variable placeholder<\/p><\/div>\n
SQL Like Query Language using SSIS PowerPack<\/h2>\n
SSIS PowerPack includes an SSIS MongoDB Source<\/a> that supports a SQL-like query language for MongoDB. It automatically converts SQL queries to correct MongoDB JSON Query syntax. See the screenshot below. Here you can write an SQL Query. If you want to use a nested array element as a column name, you must use arrayname.elementname, e.g., LocationDetails.Country<\/strong><\/p>\n
\"\"<\/a>
MongoDB SQL Query Language – SSIS MongoDB Source<\/p><\/div>\n
Download FREE SSIS Components<\/h2>\n
<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"
Introduction ZappySys provides high-performance drag-and-drop connectors for MongoDB Integration. In our previous post, we discussed how to query\/load MongoDB data (Insert, Update, Delete, Upsert).\u00a0In this post, you will see how to\u00a0query MongoDB by date\u00a0(or ISODate) using the\u00a0SSIS MongoDB Source.\u00a0To see full details about possible query syntax,\u00a0see online help. MongoDB doesn’t have SQL Like query language […]<\/p>\n","protected":false},"author":1,"featured_media":2115,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[45],"tags":[46,4],"yoast_head":"\r\nHow to query MongoDB by date or ISODate | ZappySys Blog<\/title>\r\n<meta name=\"description\" content=\"In this post you will learn how to query MongoDB by date (or ISODate). You will also learn how to use SQL Query Syntax to query MongoDB Documents.\" \/>\r\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\r\n<link rel=\"canonical\" href=\"https:\/\/zappysys.com\/blog\/how-to-query-mongodb-by-date-or-isodate\/\" \/>\r\n<meta property=\"og:locale\" content=\"en_US\" \/>\r\n<meta property=\"og:type\" content=\"article\" \/>\r\n<meta property=\"og:title\" content=\"How to query MongoDB by date or ISODate | ZappySys Blog\" \/>\r\n<meta property=\"og:description\" content=\"In this post you will learn how to query MongoDB by date (or ISODate). You will also learn how to use SQL Query Syntax to query MongoDB Documents.\" \/>\r\n<meta property=\"og:url\" content=\"https:\/\/zappysys.com\/blog\/how-to-query-mongodb-by-date-or-isodate\/\" \/>\r\n<meta property=\"og:site_name\" content=\"ZappySys Blog\" \/>\r\n<meta property=\"article:author\" content=\"https:\/\/www.facebook.com\/ZappySys\/\" \/>\r\n<meta property=\"article:published_time\" content=\"2015-11-18T00:35:19+00:00\" \/>\r\n<meta property=\"article:modified_time\" content=\"2025-10-17T12:52:58+00:00\" \/>\r\n<meta property=\"og:image\" content=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2017\/08\/mongodb-logo.png\" \/>\r\n\t<meta property=\"og:image:width\" content=\"88\" \/>\r\n\t<meta property=\"og:image:height\" content=\"88\" \/>\r\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\r\n<meta name=\"author\" content=\"ZappySys\" \/>\r\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\r\n<meta name=\"twitter:creator\" content=\"@https:\/\/twitter.com\/zappysys\/\" \/>\r\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"ZappySys\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"5 minutes\" \/>\r\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/zappysys.com\/blog\/how-to-query-mongodb-by-date-or-isodate\/\",\"url\":\"https:\/\/zappysys.com\/blog\/how-to-query-mongodb-by-date-or-isodate\/\",\"name\":\"How to query MongoDB by date or ISODate | ZappySys Blog\",\"isPartOf\":{\"@id\":\"https:\/\/zappysys.com\/blog\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/zappysys.com\/blog\/how-to-query-mongodb-by-date-or-isodate\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/zappysys.com\/blog\/how-to-query-mongodb-by-date-or-isodate\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2017\/08\/mongodb-logo.png\",\"datePublished\":\"2015-11-18T00:35:19+00:00\",\"dateModified\":\"2025-10-17T12:52:58+00:00\",\"author\":{\"@id\":\"https:\/\/zappysys.com\/blog\/#\/schema\/person\/2756c237457fbc95d82cb38962f81f82\"},\"description\":\"In this post you will learn how to query MongoDB by date (or ISODate). You will also learn how to use SQL Query Syntax to query MongoDB Documents.\",\"breadcrumb\":{\"@id\":\"https:\/\/zappysys.com\/blog\/how-to-query-mongodb-by-date-or-isodate\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/zappysys.com\/blog\/how-to-query-mongodb-by-date-or-isodate\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/zappysys.com\/blog\/how-to-query-mongodb-by-date-or-isodate\/#primaryimage\",\"url\":\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2017\/08\/mongodb-logo.png\",\"contentUrl\":\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2017\/08\/mongodb-logo.png\",\"width\":88,\"height\":88},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/zappysys.com\/blog\/how-to-query-mongodb-by-date-or-isodate\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/zappysys.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"How to query MongoDB by date or ISODate\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/zappysys.com\/blog\/#website\",\"url\":\"https:\/\/zappysys.com\/blog\/\",\"name\":\"ZappySys Blog\",\"description\":\"SSIS \/ ODBC Drivers \/ API Connectors for JSON, XML, Azure, Amazon AWS, Salesforce, MongoDB and more\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/zappysys.com\/blog\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/zappysys.com\/blog\/#\/schema\/person\/2756c237457fbc95d82cb38962f81f82\",\"name\":\"ZappySys\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/zappysys.com\/blog\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/5c9be148088ba9b8af8e955c5f7c22b5?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/5c9be148088ba9b8af8e955c5f7c22b5?s=96&d=mm&r=g\",\"caption\":\"ZappySys\"},\"sameAs\":[\"http:\/\/www.zappysys.com\/\",\"https:\/\/www.facebook.com\/ZappySys\/\",\"https:\/\/twitter.com\/https:\/\/twitter.com\/zappysys\/\"],\"url\":\"https:\/\/zappysys.com\/blog\/author\/admin\/\"}]}<\/script>\r\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"How to query MongoDB by date or ISODate | ZappySys Blog","description":"In this post you will learn how to query MongoDB by date (or ISODate). You will also learn how to use SQL Query Syntax to query MongoDB Documents.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/zappysys.com\/blog\/how-to-query-mongodb-by-date-or-isodate\/","og_locale":"en_US","og_type":"article","og_title":"How to query MongoDB by date or ISODate | ZappySys Blog","og_description":"In this post you will learn how to query MongoDB by date (or ISODate). You will also learn how to use SQL Query Syntax to query MongoDB Documents.","og_url":"https:\/\/zappysys.com\/blog\/how-to-query-mongodb-by-date-or-isodate\/","og_site_name":"ZappySys Blog","article_author":"https:\/\/www.facebook.com\/ZappySys\/","article_published_time":"2015-11-18T00:35:19+00:00","article_modified_time":"2025-10-17T12:52:58+00:00","og_image":[{"width":88,"height":88,"url":"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2017\/08\/mongodb-logo.png","type":"image\/png"}],"author":"ZappySys","twitter_card":"summary_large_image","twitter_creator":"@https:\/\/twitter.com\/zappysys\/","twitter_misc":{"Written by":"ZappySys","Est. reading time":"5 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/zappysys.com\/blog\/how-to-query-mongodb-by-date-or-isodate\/","url":"https:\/\/zappysys.com\/blog\/how-to-query-mongodb-by-date-or-isodate\/","name":"How to query MongoDB by date or ISODate | ZappySys Blog","isPartOf":{"@id":"https:\/\/zappysys.com\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/zappysys.com\/blog\/how-to-query-mongodb-by-date-or-isodate\/#primaryimage"},"image":{"@id":"https:\/\/zappysys.com\/blog\/how-to-query-mongodb-by-date-or-isodate\/#primaryimage"},"thumbnailUrl":"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2017\/08\/mongodb-logo.png","datePublished":"2015-11-18T00:35:19+00:00","dateModified":"2025-10-17T12:52:58+00:00","author":{"@id":"https:\/\/zappysys.com\/blog\/#\/schema\/person\/2756c237457fbc95d82cb38962f81f82"},"description":"In this post you will learn how to query MongoDB by date (or ISODate). You will also learn how to use SQL Query Syntax to query MongoDB Documents.","breadcrumb":{"@id":"https:\/\/zappysys.com\/blog\/how-to-query-mongodb-by-date-or-isodate\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/zappysys.com\/blog\/how-to-query-mongodb-by-date-or-isodate\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/zappysys.com\/blog\/how-to-query-mongodb-by-date-or-isodate\/#primaryimage","url":"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2017\/08\/mongodb-logo.png","contentUrl":"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2017\/08\/mongodb-logo.png","width":88,"height":88},{"@type":"BreadcrumbList","@id":"https:\/\/zappysys.com\/blog\/how-to-query-mongodb-by-date-or-isodate\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/zappysys.com\/blog\/"},{"@type":"ListItem","position":2,"name":"How to query MongoDB by date or ISODate"}]},{"@type":"WebSite","@id":"https:\/\/zappysys.com\/blog\/#website","url":"https:\/\/zappysys.com\/blog\/","name":"ZappySys Blog","description":"SSIS \/ ODBC Drivers \/ API Connectors for JSON, XML, Azure, Amazon AWS, Salesforce, MongoDB and more","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/zappysys.com\/blog\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/zappysys.com\/blog\/#\/schema\/person\/2756c237457fbc95d82cb38962f81f82","name":"ZappySys","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/zappysys.com\/blog\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/5c9be148088ba9b8af8e955c5f7c22b5?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/5c9be148088ba9b8af8e955c5f7c22b5?s=96&d=mm&r=g","caption":"ZappySys"},"sameAs":["http:\/\/www.zappysys.com\/","https:\/\/www.facebook.com\/ZappySys\/","https:\/\/twitter.com\/https:\/\/twitter.com\/zappysys\/"],"url":"https:\/\/zappysys.com\/blog\/author\/admin\/"}]}},"_links":{"self":[{"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/posts\/181"}],"collection":[{"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/comments?post=181"}],"version-history":[{"count":14,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/posts\/181\/revisions"}],"predecessor-version":[{"id":11577,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/posts\/181\/revisions\/11577"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/media\/2115"}],"wp:attachment":[{"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/media?parent=181"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/categories?post=181"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/tags?post=181"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}