{"id":140,"date":"2015-11-04T19:57:42","date_gmt":"2015-11-04T19:57:42","guid":{"rendered":"http:\/\/zappysys.com\/blog\/?p=140"},"modified":"2017-05-09T20:53:55","modified_gmt":"2017-05-09T20:53:55","slug":"ssis-yyyymmdd-date-format-using-ssis-expression","status":"publish","type":"post","link":"https:\/\/zappysys.com\/blog\/ssis-yyyymmdd-date-format-using-ssis-expression\/","title":{"rendered":"SSIS YYYYMMDD date format using SSIS expression"},"content":{"rendered":"<h2>Format Date &#8211; SSIS YYYYMMDD from date<\/h2>\n<p>Many times you have to create file paths with timestamp using <em>SSIS Expression<\/em>. You can use SSIS Expressions<br \/>\nBelow is simple expression which will produce file path with current timestamp<\/p>\n<pre>\r\n\"C:\\\\Folder\\\\MyFile_\"\r\n+ (DT_STR, 4, 1252) DATEPART(\"yyyy\" , GETDATE())\r\n+ RIGHT(\"0\" + (DT_STR, 2, 1252) DATEPART(\"mm\" , GETDATE()), 2)\r\n+ RIGHT(\"0\" + (DT_STR, 2, 1252) DATEPART(\"dd\" , GETDATE()), 2) + \".json\"\r\n<\/pre>\n<p><b>Sample Output<\/b><br \/>\n<code>C:\\Folder\\MyFile_20151231.json<\/code><\/p>\n<h2>Download FREE SSIS Components<\/h2>\n<p><a href=\"https:\/\/zappysys.com\/products\/ssis-powerpack\/\"><img decoding=\"async\" src=\"\/\/zappysys.com\/wp-content\/uploads\/2016\/10\/slider-ssis-powerpack-1.png\"><\/a><\/p>\n<h2>Convert Date &#8211; SSIS YYYYMMDD to Date<\/h2>\n<pre>\r\n--\/\/ For Control Flow \/\/---\r\n(DT_DATE)(SUBSTRING(@[yourDateAsStr],1,4) \r\n+ \"-\" + SUBSTRING(@[yourDateAsStr],5,2) \r\n+ \"-\" + SUBSTRING(@[yourDateAsStr],7,2))\r\n\r\n--\/\/ For Data Flow \/\/--\r\n(DT_DATE)(SUBSTRING([field],1,4) \r\n+ \"-\" + SUBSTRING([field],5,2) \r\n+ \"-\" + SUBSTRING([field],7,2))\r\n<\/pre>\n<h2>Set SSIS Expression to format property with datetime<\/h2>\n<p>Below is example of how to set <a href=\"\/\/zappysys.com\/products\/ssis-powerpack\/ssis-rest-api-web-service-task\/\">SSIS Rest API Task<\/a> Property expression<\/p>\n<div id=\"attachment_144\" style=\"width: 742px\" class=\"wp-caption alignnone\"><a href=\"\/\/zappysys.com\/blog\/wp-content\/uploads\/2015\/11\/ssis-yyyymmdd-date-format-expression-convert-date.jpg\"><img loading=\"lazy\" decoding=\"async\" aria-describedby=\"caption-attachment-144\" class=\"size-full wp-image-144\" src=\"\/\/zappysys.com\/blog\/wp-content\/uploads\/2015\/11\/ssis-yyyymmdd-date-format-expression-convert-date.jpg\" alt=\"SSIS YYYYMMDD date format - Use datetime in file path \" width=\"732\" height=\"552\" srcset=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2015\/11\/ssis-yyyymmdd-date-format-expression-convert-date.jpg 732w, https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2015\/11\/ssis-yyyymmdd-date-format-expression-convert-date-300x226.jpg 300w\" sizes=\"(max-width: 732px) 100vw, 732px\" \/><\/a><p id=\"caption-attachment-144\" class=\"wp-caption-text\">SSIS YYYYMMDD date format &#8211; Use datetime in file path<\/p><\/div>\n<h2>Variable Placeholder Approach to format datetime in SSIS<\/h2>\n<p>Using Variable Placeholders in <a href=\"\/\/zappysys.com\/products\/ssis-powerpack\/\">SSIS PowerPack Tasks<\/a><\/p>\n<p><img decoding=\"async\" class=\" \" title=\"SSIS Send HTML Email Task - ssis sending results as body of email\" src=\"\/\/zappysys.com\/onlinehelp\/ssis-powerpack\/scr\/images\/send-html-email-task\/ssis-send-html-email-task-variables-placeholders.png\" alt=\"SSIS Send HTML Email Task - ssis sending results as body of email\" width=\"730\" \/><\/p>\n<p class=\"wp-caption-text\">SSIS Send HTML Email Task \u2013 Using Variable Placeholders (Recordset Resultset in Body)<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Format Date &#8211; SSIS YYYYMMDD from date Many times you have to create file paths with timestamp using SSIS Expression. You can use SSIS Expressions Below is simple expression which will produce file path with current timestamp &#8220;C:\\\\Folder\\\\MyFile_&#8221; + (DT_STR, 4, 1252) DATEPART(&#8220;yyyy&#8221; , GETDATE()) + RIGHT(&#8220;0&#8221; + (DT_STR, 2, 1252) DATEPART(&#8220;mm&#8221; , GETDATE()), 2) [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":144,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[8],"tags":[36,3,12],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v22.3 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\r\n<title>SSIS YYYYMMDD date format using SSIS expression | ZappySys Blog<\/title>\r\n<meta name=\"description\" content=\"SSIS Expression to convert Date format to YYYYMMDD in SSIS. Also learn How to convert SSIS YYYYMMDD to date.\" \/>\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\/ssis-yyyymmdd-date-format-using-ssis-expression\/\" \/>\r\n<meta property=\"og:locale\" content=\"en_US\" \/>\r\n<meta property=\"og:type\" content=\"article\" \/>\r\n<meta property=\"og:title\" content=\"SSIS YYYYMMDD date format using SSIS expression | ZappySys Blog\" \/>\r\n<meta property=\"og:description\" content=\"SSIS Expression to convert Date format to YYYYMMDD in SSIS. Also learn How to convert SSIS YYYYMMDD to date.\" \/>\r\n<meta property=\"og:url\" content=\"https:\/\/zappysys.com\/blog\/ssis-yyyymmdd-date-format-using-ssis-expression\/\" \/>\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-04T19:57:42+00:00\" \/>\r\n<meta property=\"article:modified_time\" content=\"2017-05-09T20:53:55+00:00\" \/>\r\n<meta property=\"og:image\" content=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2015\/11\/ssis-yyyymmdd-date-format-expression-convert-date.jpg\" \/>\r\n\t<meta property=\"og:image:width\" content=\"732\" \/>\r\n\t<meta property=\"og:image:height\" content=\"552\" \/>\r\n\t<meta property=\"og:image:type\" content=\"image\/jpeg\" \/>\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=\"1 minute\" \/>\r\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/zappysys.com\/blog\/ssis-yyyymmdd-date-format-using-ssis-expression\/\",\"url\":\"https:\/\/zappysys.com\/blog\/ssis-yyyymmdd-date-format-using-ssis-expression\/\",\"name\":\"SSIS YYYYMMDD date format using SSIS expression | ZappySys Blog\",\"isPartOf\":{\"@id\":\"https:\/\/zappysys.com\/blog\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/zappysys.com\/blog\/ssis-yyyymmdd-date-format-using-ssis-expression\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/zappysys.com\/blog\/ssis-yyyymmdd-date-format-using-ssis-expression\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2015\/11\/ssis-yyyymmdd-date-format-expression-convert-date.jpg\",\"datePublished\":\"2015-11-04T19:57:42+00:00\",\"dateModified\":\"2017-05-09T20:53:55+00:00\",\"author\":{\"@id\":\"https:\/\/zappysys.com\/blog\/#\/schema\/person\/2756c237457fbc95d82cb38962f81f82\"},\"description\":\"SSIS Expression to convert Date format to YYYYMMDD in SSIS. Also learn How to convert SSIS YYYYMMDD to date.\",\"breadcrumb\":{\"@id\":\"https:\/\/zappysys.com\/blog\/ssis-yyyymmdd-date-format-using-ssis-expression\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/zappysys.com\/blog\/ssis-yyyymmdd-date-format-using-ssis-expression\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/zappysys.com\/blog\/ssis-yyyymmdd-date-format-using-ssis-expression\/#primaryimage\",\"url\":\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2015\/11\/ssis-yyyymmdd-date-format-expression-convert-date.jpg\",\"contentUrl\":\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2015\/11\/ssis-yyyymmdd-date-format-expression-convert-date.jpg\",\"width\":732,\"height\":552,\"caption\":\"SSIS YYYYMMDD date format - Use datetime in file path\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/zappysys.com\/blog\/ssis-yyyymmdd-date-format-using-ssis-expression\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/zappysys.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"SSIS YYYYMMDD date format using SSIS expression\"}]},{\"@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":"SSIS YYYYMMDD date format using SSIS expression | ZappySys Blog","description":"SSIS Expression to convert Date format to YYYYMMDD in SSIS. Also learn How to convert SSIS YYYYMMDD to date.","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\/ssis-yyyymmdd-date-format-using-ssis-expression\/","og_locale":"en_US","og_type":"article","og_title":"SSIS YYYYMMDD date format using SSIS expression | ZappySys Blog","og_description":"SSIS Expression to convert Date format to YYYYMMDD in SSIS. Also learn How to convert SSIS YYYYMMDD to date.","og_url":"https:\/\/zappysys.com\/blog\/ssis-yyyymmdd-date-format-using-ssis-expression\/","og_site_name":"ZappySys Blog","article_author":"https:\/\/www.facebook.com\/ZappySys\/","article_published_time":"2015-11-04T19:57:42+00:00","article_modified_time":"2017-05-09T20:53:55+00:00","og_image":[{"width":732,"height":552,"url":"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2015\/11\/ssis-yyyymmdd-date-format-expression-convert-date.jpg","type":"image\/jpeg"}],"author":"ZappySys","twitter_card":"summary_large_image","twitter_creator":"@https:\/\/twitter.com\/zappysys\/","twitter_misc":{"Written by":"ZappySys","Est. reading time":"1 minute"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/zappysys.com\/blog\/ssis-yyyymmdd-date-format-using-ssis-expression\/","url":"https:\/\/zappysys.com\/blog\/ssis-yyyymmdd-date-format-using-ssis-expression\/","name":"SSIS YYYYMMDD date format using SSIS expression | ZappySys Blog","isPartOf":{"@id":"https:\/\/zappysys.com\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/zappysys.com\/blog\/ssis-yyyymmdd-date-format-using-ssis-expression\/#primaryimage"},"image":{"@id":"https:\/\/zappysys.com\/blog\/ssis-yyyymmdd-date-format-using-ssis-expression\/#primaryimage"},"thumbnailUrl":"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2015\/11\/ssis-yyyymmdd-date-format-expression-convert-date.jpg","datePublished":"2015-11-04T19:57:42+00:00","dateModified":"2017-05-09T20:53:55+00:00","author":{"@id":"https:\/\/zappysys.com\/blog\/#\/schema\/person\/2756c237457fbc95d82cb38962f81f82"},"description":"SSIS Expression to convert Date format to YYYYMMDD in SSIS. Also learn How to convert SSIS YYYYMMDD to date.","breadcrumb":{"@id":"https:\/\/zappysys.com\/blog\/ssis-yyyymmdd-date-format-using-ssis-expression\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/zappysys.com\/blog\/ssis-yyyymmdd-date-format-using-ssis-expression\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/zappysys.com\/blog\/ssis-yyyymmdd-date-format-using-ssis-expression\/#primaryimage","url":"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2015\/11\/ssis-yyyymmdd-date-format-expression-convert-date.jpg","contentUrl":"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2015\/11\/ssis-yyyymmdd-date-format-expression-convert-date.jpg","width":732,"height":552,"caption":"SSIS YYYYMMDD date format - Use datetime in file path"},{"@type":"BreadcrumbList","@id":"https:\/\/zappysys.com\/blog\/ssis-yyyymmdd-date-format-using-ssis-expression\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/zappysys.com\/blog\/"},{"@type":"ListItem","position":2,"name":"SSIS YYYYMMDD date format using SSIS expression"}]},{"@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\/140"}],"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=140"}],"version-history":[{"count":2,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/posts\/140\/revisions"}],"predecessor-version":[{"id":1139,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/posts\/140\/revisions\/1139"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/media\/144"}],"wp:attachment":[{"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/media?parent=140"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/categories?post=140"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/tags?post=140"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}