Introduction
When and how to use sql formatting functions to format datetime values when it is being used by the application that is target to be run on different locale and culture.
Problem Statement
If schema of a TimeTable is as follows-
Activity StartDate StartTime
-----------------------------------------------------------
Meeting 03/02/2008 00:00:00 01/01/1900 14:30:25
Lunch Break 03/02/2008 00:00:00 01/01/1900 17:15:00
Now a requirement is to get sum(03/02/2008 14:30:25) of values of startdate and starttime fields in a new field using SELECT query one might use CAST/CONVERT function as follows
SELECT StartDate,StartTime,cast((convert(varchar,StartDate,101) + space(1) + convert(varchar,StartTime,8)) as Datetime) as Datetime FROM [TimeTable]
While writing sql queries, for adding date and time to create a field having combined datetime, one should be aware of using CAST & CONVERT function as shown below. Datetime field is used in datetime calculations or updating other business object's state hence producing wrong results or datetime in format that mismatches.
This query returns date value in US format(mm/dd/yyyy) in datetime field no matter what the current locale is, hence mismatching dateformat with the other datetime types of fields(StartDate,StartTime) in result set.
Solution
.NET framework provides a base class library to manipulate datetime type of values efficiently. Although using .Net framework's built in objects and methods could fix the mismatch but not effective when date & time field is directly being used in business logic or Domain modal entities.
Instead we could use DATEADD and DATEPART SQL methods to create a datetime field having addition values of date & time. It returns datetime field in current select locale, hence reducing the locale date format mismatch problems.
SELECT StartDate,StartTime,dateadd(hh,datepart(hh,StartTime),dateadd(mi,datepart(mi,StartTime),dateadd(ss,datepart(ss,StartTime),StartDate))) as Datetime FROM [TimeTable]
Summary
Writing queries using dateadd,datepart functions will format datetime values efficiently according to the culture query is being executed in. It would reduce the extra effort of converting them to correct format at code side hence reducing error prone implementation.
