Return to Answer

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).

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).

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, CONVERT(CHAR(23), y)), 126) 
  ELSE y END
  FROM @x;

SELECT CONVERT(VARCHAR(4000), 
  CASE WHEN ISDATE(CONVERT(CHAR(23), y)) = 1 
  THEN CONVERT(DATETIME, CONVERT(CHAR(23, 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.

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;

CAST simply doesn't support the ability. 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 VALUES(GETDATE());
INSERT @x VALUES('foo');

SELECT CASE WHEN ISDATE(CONVERT(CHAR(23), y)) = 1 
  THEN CONVERT(CHAR(23), CONVERT(DATETIME, CONVERT(CHAR(23),y)), 126) 
  ELSE y END
  FROM @x;

SELECT CONVERT(VARCHAR(4000), 
  CASE WHEN ISDATE(CONVERT(CHAR(23), y)) = 1 
  THEN CONVERT(DATETIME, CONVERT(CHAR(23,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.

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.

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, CONVERT(CHAR(23), y)), 126) 
  ELSE y END
  FROM @x;

SELECT CONVERT(VARCHAR(4000), 
  CASE WHEN ISDATE(CONVERT(CHAR(23), y)) = 1 
  THEN CONVERT(DATETIME, CONVERT(CHAR(23, 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.

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.