I created a simple scalar function in SQL that takes in the date as a string and restricts it to 23 characters. Then a CONVERT to datetime is used if the string is not null or empty. Then everywhere I have a date coming in I wrap this function around it.
Example:
SELECT [dbo].GetFormattedDate('2013-07-01T14:27:00.434')
,[dbo].GetFormattedDate('')
,[dbo].GetFormattedDate(NULL)
,[dbo].GetFormattedDate('2013-07-01T14:27:00.434Z')
,[dbo].GetFormattedDate('2013-07-01T14:27:00.434445Z')
,[dbo].GetFormattedDate('2013-07-01')
Function Code:
CREATE FUNCTION [dbo].[GetFormattedDate]
(
@p_DateString varchar(23)
)
RETURNS datetime
AS
BEGIN
DECLARE @returnDatetime as datetime;
IF ((@p_DateString IS NULL) OR (@p_DateString = ''))
BEGIN
SET @returnDatetime = NULL;
END
ELSE
BEGIN
SET @returnDatetime = CONVERT(datetime, @p_DateString);
END
RETURN @returnDatetime;
END