Is it possible to change the default style used by CAST/CONVERT when converting DATETIME?
CAST
simply doesn't support the ability to do anything other than a generic conversion, without any flexibility other than inherent ones (e.g. language or dateformat settings, which have to do with how a date string is interpreted, not how much precision it has). Generic conversions of datetime -> string loses precision, regardless of the method. None of these will have seconds:
PRINT GETDATE();
SELECT CAST(GETDATE() AS VARCHAR(23));
SELECT CONVERT(VARCHAR(23), GETDATE());
The answer is to use CONVERT
with an appropriate length and a style number (and there is no reason to use VARCHAR
for this conversion in isolation).
SELECT CONVERT(CHAR(23), GETDATE(), 126);
Sorry, but there is no server-, database-, or schema-level setting that will make this happen automatically (or change the way CAST
works).
Regarding the specific problem you think you have (you can't use CONVERT
because of errors with SQL_VARIANT
arguments), you can cast as DATETIME
within a CASE
expression to avoid getting errors on direct conversion of non-convertible values. Either of these forms will work:
DECLARE @x TABLE(y SQL_VARIANT);
INSERT @x(y) VALUES(GETDATE());
INSERT @x(y) VALUES('foo');
SELECT CASE WHEN ISDATE(CONVERT(CHAR(23), y)) = 1
THEN CONVERT(CHAR(23), CONVERT(DATETIME, y), 126)
ELSE y END
FROM @x;
SELECT CONVERT(VARCHAR(4000),
CASE WHEN ISDATE(CONVERT(CHAR(23), y)) = 1
THEN CONVERT(DATETIME, y) ELSE y END, 126)
FROM @x;
And before you say that must be inefficient, you're using SQL_VARIANT
and scalar user-defined functions. These extra converts of singleton values will be a mere drop in the bucket.
A disclaimer: you could still end up with errors using this approach, since the ISDATE()
test is only checking the first 23 characters of what could be a valid datetime string concatenated with garbage. For example:
INSERT @x(y) VALUES('2000-01-01T00:00:00.000 garbage');
Using the same code as above will lead to:
Msg 241, Level 16, State 1
Conversion failed when converting date and/or time from character string.
So a slightly safer approach would be that suggested by Paul below, using the metadata that SQL_VARIANT
stores to ensure you only try to convert data with a date/time base type. This allows you to ignore string lengths etc. as well (except for on output).
DECLARE @x TABLE(y SQL_VARIANT);
INSERT @x(y) VALUES(GETDATE());
INSERT @x(y) VALUES('foo');
INSERT @x(y) VALUES('2000-01-01T00:00:00.000 garbage');
SELECT CASE
WHEN CONVERT(SYSNAME, SQL_VARIANT_PROPERTY(y, 'BaseType')) LIKE N'%date%'
THEN CONVERT(CHAR(23), CONVERT(DATETIME, y), 126)
ELSE y END
FROM @x;
In the future, rather than just vaguely saying "I am unable to use CONVERT," show us what you're actually doing and why you actually think you can't do something. Some pretty smart folks here that can grok and analyze without having to pull teeth or make assumptions.