--- title: Handling source system dates in SQL & Excel description: "All BI systems need to manipulate source system dates in order to produce derived columns such as Month or Year, which are useful in reports or dashboards. #Design Tip 1: It is sometime very useful to" --- [Skip to content](https://help.intuitivebusinessintelligence.com/handling-source-system-dates-in-sql-excel#main-content) English Show submenu for translations ![Intuitive logo RGB-1.png\]](https://help.intuitivebusinessintelligence.com/hs-fs/hubfs/Intuitive%20logo%20RGB-1.png?height=40&name=Intuitive%20logo%20RGB-1.png) Open main navigation Close main navigation - English Show submenu for translations - [Go to weareintuitive.com](https://weareintuitive.com/) [Go to weareintuitive.com](https://weareintuitive.com/) Hello. How can we help you? - There are no suggestions because the search field is empty. 1. [Help Center](https://help.intuitivebusinessintelligence.com/?hsLang=en) 2. [Intuitive Dashboards Core Software](https://help.intuitivebusinessintelligence.com/intuitive-dashboards-core-software?hsLang=en) 3. [Datafeeds](https://help.intuitivebusinessintelligence.com/intuitive-dashboards-core-software?hsLang=en#datafeeds) # Handling source system dates in SQL & Excel All BI systems need to manipulate source system dates in order to produce derived columns such as Month or Year, which are useful in reports or dashboards. While Intuitive Dashboards provide comprehensive [**dataset functions**](https://help.intuitivebusinessintelligence.com/formatting-dates?hsLang=en) to handle date conversions etc, it is sometimes desirable to handle system dates outside Intuitive datasets. The manipulation is commonly performed within the [**datafeed**](https://help.intuitivebusinessintelligence.com/creating-a-datafeed?hsLang=en)from the source system, either within the extracting SQL statement or in calculated columns within the source spreadsheet.   The advantage of preparing descriptive information based on dates within the datafeed are: - The logic does not need to be understood by the user building components and dashboards. - Performance of the system is improved since the calculation of date-derived functions need not be performed each time a component is refreshed. - More sophisticated derived calculations can be used for financial year, academic year, financial week etc. Such derivations are highly bespoke to each organisation and require the capability of SQL or Excel to process the source information. **#Design Tip 1**: It is sometime very useful to have a Date/Time stamp as a column in a datafeed to record the Date/Time the query was last run eg. *SELECT GETDATE() AS DateTimeStamp FROM Tablename* **#Design Tip 2:** Having a Date/Time stamp column present in a datafeed allows *Elapsed Time* calculations to be performed in the dataset using the inbuilt *datediff* date function.   The following list describes some of the more popular functions and shows how they are implemented in both SQL Server and Excel. Other database engines and programming languages provide similar options - please refer to your documentation.   **Year** SQL Server function: DATEPART(yyyy, MyDateColumn) AS Year Excel function: =YEAR(MyDateCell) Purpose: Allows information within a dataset to be aggregated up to year level within a component.   **Years Elapsed** SQL Server function: DATEDIFF(yy, MyDateColumn, GETDATE()) AS YearsElapsed Excel function: =DATEDIF(MyDateCell,TODAY(),"y") Purpose: Allows a filter to be set within the dashboard limiting results to a certain range of calendar years relative to the current year. Common uses would be YearsElapsed=0 (for the current calendar year / year to date); YearsElapsed=1 for last calendar year; YearsElapsed\<2 for the current and last calendar years.   **Period** SQL Server function: CONVERT(varchar(7), MyDateColumn, 23) AS Period Excel function: =CONCATENATE(TEXT(YEAR(MyDateCell),"0000"),"-",TEXT(MONTH(MyDateCell),"00")) Purpose: Creates an x-axis attribute of the format eg 2014-07 (for July 2014).  Allows information within a dataset to be aggregated up to month level within a component. This should be the first choice of attributes to display on the x axis of a chart since it allows months to be displayed straddling multi-year boundaries. Also it provides assurance to the user that they are looking at one particular month within one year.   **Periods Elapsed** SQL Server function: DATEDIFF(mm, MyDateColumn, GETDATE()) AS PeriodsElapsed Excel function: =DATEDIF(MyDateCell,TODAY(),"m") Purpose: Allows a filter to be set within the dashboard limiting results to the last n months. Common uses would be PeriodsElapsed = 0 (for current month); PeriodsElapsed = 1 (For last month); PeriodsElapsed BETWEEN 1 AND 12 for the last rolling 12 month period. **Month Number** SQL Server function: DATEPART(mm, MyDateColumn) AS MonthNumber Excel function: =MONTH(MyDateCell) Purpose: Allows a filter to be set within the dashboard having, say Monthnumber\<10 or MonthNumber BETWEEN 4 AND 6 or MonthNumber = 4. Can also be used for x axis on a component that does not span multiple years.     **Week Number** SQL Server function: DATEPART(ww, MyDateColumn) AS WeekNumber Excel function: =WEEKNUM(MyDateCell) Purpose: Displays 1-53 within the calendar year; useful as an x-axis value for organisations having weekly reporting, or as a filter if the dashboard is to show the past n weeks etc. **Year-Week** SQL Server function: CAST(DATEPART(yyyy, MyDateColumn ) AS char(4)) + '-' + RIGHT (CONVERT (varchar, 0) + CONVERT (VARCHAR, DATEPART(wk, MyDateColumn)), 2) AS \[Year-Week\] Excel function: =CONCATENATE(TEXT(YEAR(B6),"0000"),"-",TEXT(WEEKNUM(B6),"00")) Purpose: Creates an x-axis attribute of the format eg 2014-45 (for week 45 of 2014).  Allows information within a dataset to be aggregated up to week level within a component. This should be the first choice of attributes to display on the x axis of a chart since it allows weeks to be displayed straddling multi-year boundaries. Also it provides assurance to the user that they are looking at one particular week within one year.   **Weeks Elapsed** SQL Server function: DATEDIFF(ww, MyDateColumn, GETDATE()) AS WeeksElapsed Excel function: =ROUNDDOWN((TODAY()-MyDateCell)/7,0) Purpose: Allows a filter to be set within the dashboard limiting results to the last n weeks. Common uses would be WeeksElapsed = 0 (for current week); PeriodsElapsed = 1 (For last week); WeeksElapsed BETWEEN 1 AND 52 for the last rolling 52-week period.   **Date (no time component)** SQL Server function: DATEADD (dd, DATEDIFF (dd, 0, MyDateColumn), 0) Excel function: =INT(MyDateCell) Purpose: Removes the time component from a datetime type source column having both dates and times present. This allows consistent drilldown and aggregation of information within the dataset without the dashboard server considering two dates to be different values (since they each have a different time component).   **Hour** SQL Server function: DATEPART(hh, MyDateColumn) Excel function: =HOUR(MyDateCell) Purpose: For source system datetime columns containing a time component, it may be useful to extract the hour for use in components eg as a drilldown beneath ‘number of calls by day’.   **An example of an extract from SQL Server using the above functions:** *SELECT* *Region, EventType, JobNumber, Status, Consultant, UserLeft, Client, JobType, JobTitle, Sector, Company, VacancyLocation, VacancyDetails, PositionType,  UserID, CompanyId, Office, Country, TeamName, TeamManager, VacancyID, InterviewId, CandidateId, CandidateName, InterviewNo, Placements, PlacementDiscipline, InvoiceNumber, FeeTotal, Positions, FeeSales, FeeWIP, FeeConvertedSales, MeetingID, Interview1\_ID, Interview2\_ID, Interview3OrMore\_ID, CallsCount, CallsDuration, EventDate, DATEPART(yyyy, EventDate) AS EventYear, DATEPART(mm, EventDate) AS EventMonthnumber, CONVERT(varchar(7), EventDate, 23) AS \[EventYear-Month\], DATEDIFF(mm, EventDate, GETDATE()) AS EventMonthsElapsed,DATEDIFF(yy, EventDate, GETDATE()) AS EventYearsElapsed, DATEPART(ww, EventDate) AS EventWeeknumber, CAST(DATEPART(yyyy, EventDate ) AS char(4)) + '-' + RIGHT (CONVERT (varchar, 0) + CONVERT (VARCHAR, DATEPART(wk,EventDate)), 2) AS \[EventYear-Week\], DATEDIFF(ww, EventDate, GETDATE()) AS EventWeeksElapsed* *FROM dbo.View\_AllRegions\_Union* - [Intuitive Dashboards Core Software](https://help.intuitivebusinessintelligence.com/intuitive-dashboards-core-software?hsLang=en#main-content) - [Getting Started](https://help.intuitivebusinessintelligence.com/intuitive-dashboards-core-software?hsLang=en#getting-started) - [Viewing Dashboards](https://help.intuitivebusinessintelligence.com/intuitive-dashboards-core-software?hsLang=en#viewing-dashboards) - [Interacting with dashboards](https://help.intuitivebusinessintelligence.com/intuitive-dashboards-core-software?hsLang=en#interacting-with-dashboards) - [Filters](https://help.intuitivebusinessintelligence.com/intuitive-dashboards-core-software?hsLang=en#filters) - [Connections](https://help.intuitivebusinessintelligence.com/intuitive-dashboards-core-software?hsLang=en#connections) - [Datafeeds](https://help.intuitivebusinessintelligence.com/intuitive-dashboards-core-software?hsLang=en#datafeeds) - [Datasets](https://help.intuitivebusinessintelligence.com/intuitive-dashboards-core-software?hsLang=en#datasets) - [Components](https://help.intuitivebusinessintelligence.com/intuitive-dashboards-core-software?hsLang=en#components) - [Creating Dashboards and Components - Overview](https://help.intuitivebusinessintelligence.com/intuitive-dashboards-core-software?hsLang=en#creating-dashboards-and-components-overview) - [Creating Components](https://help.intuitivebusinessintelligence.com/intuitive-dashboards-core-software?hsLang=en#creating-components) - [Creating a Dashboard](https://help.intuitivebusinessintelligence.com/intuitive-dashboards-core-software?hsLang=en#creating-a-dashboard) - [Administering the System](https://help.intuitivebusinessintelligence.com/intuitive-dashboards-core-software?hsLang=en#administering-the-system) - [Administering Users](https://help.intuitivebusinessintelligence.com/intuitive-dashboards-core-software?hsLang=en#administering-users) - [Administering Groups](https://help.intuitivebusinessintelligence.com/intuitive-dashboards-core-software?hsLang=en#administering-groups) - [Security Filters - V5.2 and earlier](https://help.intuitivebusinessintelligence.com/intuitive-dashboards-core-software?hsLang=en#security-filters-v5-2-and-earlier) - [Security Filters - V5.3 Onward](https://help.intuitivebusinessintelligence.com/intuitive-dashboards-core-software?hsLang=en#security-filters-v5-3-onward) - [Embedding Dashboards](https://help.intuitivebusinessintelligence.com/intuitive-dashboards-core-software?hsLang=en#embedding-dashboards) - [Configuration File](https://help.intuitivebusinessintelligence.com/intuitive-dashboards-core-software?hsLang=en#configuration-file) - [Installation & Configuration Guide](https://help.intuitivebusinessintelligence.com/intuitive-dashboards-core-software?hsLang=en#installation-configuration-guide) - [Version History](https://help.intuitivebusinessintelligence.com/intuitive-dashboards-core-software?hsLang=en#version-history) - [Intuitive for PaperCut V2](https://help.intuitivebusinessintelligence.com/intuitive-for-papercut-v2?hsLang=en#main-content) - [Installing Intuitive for PaperCut](https://help.intuitivebusinessintelligence.com/intuitive-for-papercut-v2?hsLang=en#installing-intuitive-for-papercut) - [Viewing PaperCut Dashboards](https://help.intuitivebusinessintelligence.com/intuitive-for-papercut-v2?hsLang=en#viewing-papercut-dashboards) - [Editing PaperCut Dashboards](https://help.intuitivebusinessintelligence.com/intuitive-for-papercut-v2?hsLang=en#editing-papercut-dashboards) - [Intuitive for PaperCut Demonstration Videos](https://help.intuitivebusinessintelligence.com/intuitive-for-papercut-v2?hsLang=en#intuitive-for-papercut-demonstration-videos) - [Intuitive for PaperCut FAQs](https://help.intuitivebusinessintelligence.com/intuitive-for-papercut-v2?hsLang=en#intuitive-for-papercut-faqs) - [Intuitive for PaperCut MF V3](https://help.intuitivebusinessintelligence.com/intuitive-for-papercut-mf-v3?hsLang=en#main-content) - [Installing Intuitive for PaperCut Version 3](https://help.intuitivebusinessintelligence.com/intuitive-for-papercut-mf-v3?hsLang=en#installing-intuitive-for-papercut-version-3) - [Viewing the dashboards - Intuitive for PaperCut Version 3](https://help.intuitivebusinessintelligence.com/intuitive-for-papercut-mf-v3?hsLang=en#viewing-the-dashboards-intuitive-for-papercut-version-3) - [Editing PaperCut Dashboards](https://help.intuitivebusinessintelligence.com/intuitive-for-papercut-mf-v3?hsLang=en#editing-papercut-dashboards) - [Intuitive for PaperCut FAQs](https://help.intuitivebusinessintelligence.com/intuitive-for-papercut-mf-v3?hsLang=en#intuitive-for-papercut-faqs) - [Intuitive for SAFEQ](https://help.intuitivebusinessintelligence.com/intuitive-for-safeq?hsLang=en#main-content) - [General Help](https://help.intuitivebusinessintelligence.com/intuitive-for-safeq?hsLang=en#general-help) - [Software Installation](https://help.intuitivebusinessintelligence.com/intuitive-for-safeq?hsLang=en#software-installation) - [Solution Expertise](https://help.intuitivebusinessintelligence.com/intuitive-for-safeq?hsLang=en#solution-expertise) - [Intuitive For Managed Print Services (MPS)](https://help.intuitivebusinessintelligence.com/intuitive-for-managed-print-services-mps?hsLang=en#main-content) - [Intuitive for MPS - Overview](https://help.intuitivebusinessintelligence.com/intuitive-for-managed-print-services-mps?hsLang=en#intuitive-for-mps-overview) - [Intuitive for MPS - Installation Steps](https://help.intuitivebusinessintelligence.com/intuitive-for-managed-print-services-mps?hsLang=en#intuitive-for-mps-installation-steps) - [Intuitive for MPS - Import File Layouts](https://help.intuitivebusinessintelligence.com/intuitive-for-managed-print-services-mps?hsLang=en#intuitive-for-mps-import-file-layouts) - [Intuitive For MPS - Loading the Data Warehouse](https://help.intuitivebusinessintelligence.com/intuitive-for-managed-print-services-mps?hsLang=en#intuitive-for-mps-loading-the-data-warehouse) - [Intuitive Cloud Services](https://help.intuitivebusinessintelligence.com/intuitive-cloud-services?hsLang=en) - [Intuitive for PaperCut Hive V1](https://help.intuitivebusinessintelligence.com/intuitive-for-papercut-hive-v1?hsLang=en#main-content) - [Viewing the dashboards - Intuitive for PaperCut Hive](https://help.intuitivebusinessintelligence.com/intuitive-for-papercut-hive-v1?hsLang=en#viewing-the-dashboards-intuitive-for-papercut-hive) - [Intuitive for SAFEQ Cloud](https://help.intuitivebusinessintelligence.com/intuitive-for-safeq-cloud?hsLang=en#main-content) - [Intuitive Solution Expertise](https://help.intuitivebusinessintelligence.com/intuitive-for-safeq-cloud?hsLang=en#intuitive-solution-expertise) - [Intuitive Software Provisioning](https://help.intuitivebusinessintelligence.com/intuitive-for-safeq-cloud?hsLang=en#intuitive-software-provisioning) - [Intuitive for Docuware](https://help.intuitivebusinessintelligence.com/intuitive-for-docuware?hsLang=en) - [Intuitive for Print Management](https://help.intuitivebusinessintelligence.com/intuitive-for-print-management?hsLang=en#main-content) - [Solution Knowledge](https://help.intuitivebusinessintelligence.com/intuitive-for-print-management?hsLang=en#solution-knowledge) - [Technical Information](https://help.intuitivebusinessintelligence.com/intuitive-for-print-management?hsLang=en#technical-information) - [Intuitive for e-BRIDGE Global Print](https://help.intuitivebusinessintelligence.com/intuitive-for-e-bridge-global-print?hsLang=en) - [Intuitive for e-FOLLOW Cloud](https://help.intuitivebusinessintelligence.com/intuitive-for-e-follow-cloud?hsLang=en) - [FAQ](https://help.intuitivebusinessintelligence.com/faq?hsLang=en#main-content) - [Connections](https://help.intuitivebusinessintelligence.com/faq?hsLang=en#connections) - [General Enquiries](https://help.intuitivebusinessintelligence.com/faq?hsLang=en#general-enquiries) - [Datafeeds](https://help.intuitivebusinessintelligence.com/faq?hsLang=en#datafeeds) - [Datasets](https://help.intuitivebusinessintelligence.com/faq?hsLang=en#datasets) - [System Compatability](https://help.intuitivebusinessintelligence.com/faq?hsLang=en#system-compatability) - [Software Installation](https://help.intuitivebusinessintelligence.com/faq?hsLang=en#software-installation) [![Chill listening crop-3](https://help.intuitivebusinessintelligence.com/hs-fs/hubfs/Intuitive%20logo%20BLACK.png?width=102&height=24&name=Intuitive%20logo%20BLACK.png "Chill listening crop-3")](https://help.intuitivebusinessintelligence.com?hsLang=en) weareintuitive.com Help Center Copyright © 2026, Intuitive Business Intelligence Limited