That’ll be the date!
For consultants who work with Synergy & Globe for a long time knowledge of SQL is essential. Whether we are making reports, views or links with other applications, it’s our second language. In this post I would like to talk a bit about working with dates. There are multiple tips and tricks which come in handy.
Function dbo.Dateonly for DateTime fields
The field format Exact uses to store dates is of the type ‘DateTime’. This means that in a single field both the date and the time are stored. For example when a record was created or modified it will have the precise moment in milliseconds of when it was created. Exact also provides a function (dbo.DateOnly) which will convert this into date only.
When does this come in handy? For example, you want to get a result of all requests which where created on Feb 14th, 2019. Since the created field stores a Date and a Time, u will need a range:
Getting different parts of a date
Another common occurrence is the need to extract different information from a date. For example, the month or week number. Below is an example query which extracts different values from a date.
Queries like this work well, but in some case, for example with large result sets there can be a performance trade of. So another thing I do frequently is create a separate table with dates and additional information. The script to create the table and populate the data from 01-01-2000 to 31-12-2050, can be downloaded here: https://drive.google.com/open?id=1SkKg-CNVqN6MJ7OjZZ1OasmD1xbVhgG0
To use this, simply join the table in your query.
F.E.:
If you found this article helpful, please Like & Share!
For my previous posts see:
Exact Synergy: Email notification of new workflow
Exact Synergy & Globe: Where are my debtors!
We need more celebrations in Exact e-Synergy!
Exact Synergy: Now you see it, now you don’t
Exact Synergy: Creating extra buttons in Requests and for fun giving the buttons some color
Exact Synergy: “A Project that will not fit on one page cannot be understood.”
Exact Synergy: “No man needs a vacation so much as the man who has just had one.”
Exact Synergy: Turning free fields into hyperlinks
Displaying contact person information in an Exact Synergy Workflow Request
Getting the most out of Exact Synergy Enterprise
The security audit reports Exact Synergy Enterprise
A small trick to track changes on request definitions in Exact Synergy
I agree that using a date table with the translations and other characteristics of the date is a preferable option to using the dbo.dateonly function. You may achieve the same by using an explicit cast of the datetime value: CAST(datefield AS date) to get only the date returned. When using a datetime field in a range selection it is much better performing when using table.datefield >= '...' AND table.datefield < '...' I agree with Gert-Jan and Stefan. The two drawbacks i see are the following: 1) the table suggested also contains a field to denote if it is a Holiday date. This limits the use to only one area. As the holidays differ per country/region and sometimes even city, it is better to link that one up with a public holidays table + the resource table, which holds the specific dates per country/region/city and the employees location. 2) The range of this calendar is fixed. This is only a small issue though. It is easily extendable.
Note that your first example, a date-range performs better then calling the dbo.DateOnly for every record, and then let SQL see if the date equals the preferred date. If you really want to use a single statement, I'd prefer the DateDiff function to see if there are 0 days between the startdate and the current date. That said, still, it's better to use the check Syscreated >= '...' and Syscreated < '...' For the ISO Week and ISO Year, it's better to use the Exact functions for this (dbo.ISOWeekNumber and dbo.ISOWeekYear) although I think they only exist in Globe db's, because the you get the same results as Exact shows (fun with broken weeks where the new year starts on tuesday, Wednesday or thursday).