Custom Date and Time functions in SQL

Many of you are probably aware that you can create a function in SQL that returns a value and then use it like a column in a SQL statement.

For instance,
select WS_DateOnly(getdate())
will return a single column containing the date portion of the current date (truncating off the time).

In GP, these type of time truncations are critical because the columns contain either a date or a time, but not both.

I understand the need for using functions to keep formatting and such the same.  They can also simplify SQL statements by removing much of the calculations from the statement.

However, in the case of date and time truncation, format and calculation does not come into play at all.  The following statements are equivalent.

select WS_DateOnly(getdate())
select convert(varchar,getdate(),102)

and

select WS_TimeOnly(getdate())
select convert(varchar,getdate(),114)

The formats for the dates do not really matter other than 102 is a time-less date and 114 is a date-less time.

Here are some reasons that burying this type of calculation in a function are a bad idea:
1. SQL creates a dependency for each place the function is used.  This adds to overhead and erodes performance.
2. A developer reading the statement that uses the function does not know exactly what the function is doing unless they go open it, slowing down troubleshooting.
3. SQL traces see the function call as a separate event, cluttering up traces.


The most painful issue of all is when the function gets added to a check constraint on a table.  This creates a dependency that will crash the upgrade utilities for GP and requires manual correction.

So the takeaways are:
1. Never use functions to mask simple SQL functions like date truncation
2. Use functions sparingly
3. Never use a function in a table constraint on a GP database.