Setup a Filter on Date Column in DataFromDatabase

Posted under features on October 24 , 2019 by Nidhisha Shetty

Excel does not support Date and Time data types unlike databases. Hence, while doing a SheetKraft import of the table having a column of date type from database to Excel, the values in that column appear in number format rather than date format.

While importing a table from database having a date column set to float data type, the filter condition will have the date in number format to fetch values in that column; whereas for a table imported from database having a date column set to date data type, the filter condition will have the date in text format (for example, '03-Apr-2018) to fetch values in that column. Providing the date as 03-04-2018 in the latter will create ambiguity if 03 and 04 is the day or month.

Following steps describe the setup of filter on date column in more detail:

  1. Import the data from database to Excel.

1.1

Fig 1.1: Importing data from database

  1. In the Data From Database window, select the table from the database by clicking on the ellipsis as highlighted in the following figure:

1.2

Fig 1.2: Data From Database window

  1. Select the table from the “Select Table” window.

1.3

Fig 1.3: Selecting table

  1. Table preview is shown after selecting the table from database.

1.4

Fig 1.4: Table preview

  1. Once the data is imported from database, the date is displayed in number format; since the backend of SheetKraft stores the date in number format.

1.5

Fig 1.5: Data imported to Excel

NOTE: To display the cells in the Date column in date format, press Ctrl + Shift + 3, and press Ctrl + Shift + ~ to convert it back to number form. This is only for display purpose and has no effect on the backend of SheetKraft.

  1. For retrieving data of any particular date, for example 3rd of April 2018 (43193), add the filter condition in the text format, that is, '03-Apr-2018 as shown in figure Fig 1.6.

1.6

Fig 1.6: Filter condition

  1. Following is the output with date matching the filter condition, that is, '03-Apr-2018:

1.7

Fig 1.7: Output



TAGGED:DateData From DatabaseFilterData Type


Recent Posts


Export To Excel Using Template

February 06, 2020 bySwanand Nirgudkar

For generating custom reports with complex formatting rules, SheetKraft's ExportToExcel Using…

Continue Reading

Export to XML

February 02, 2020 bySwanand Nirgudkar

A quick reference for exporting to an XML document through SheetKraft Any form of organized or…

Continue Reading

Display Inputs and Calculate Immediately Outputs

January 20, 2020 bySwanand Nirgudkar

While running an automated process, there may arise a need for the user to get some assistance on…

Continue Reading

Exporting Double Quotes Within a Text File

November 19, 2019 byShubham Thakkar

When dealing with automatic messaging systems, we often have to use some conventions in our input…

Continue Reading

Let's talk

Address

7th floor, Unit No. 701, C Wing,
Kailas Business Park, S. Veer Savarkar Marg,
Park Site, Vikhroli (W), Mumbai-400079.

 

Phone

+91 22 62362043

 

For General Queries & Technical Support

contact@quantumphinance.com

 

For Sale and Demos

sales@quantumphinance.com