Is it possible to change the default style used by CAST/CONVERT when converting DATETIME? [closed]

Question

When you attempt to CAST a DATETIME as VARCHAR in SQL Server, the default date formatting style is a fairly inaccurate date format that loses the precision of both seconds and milliseconds. For example:

SELECT CAST(GETDATE() AS VARCHAR)
-- May  8 2015  9:13AM

Is there a way to change the default style used? For example, I would like to use style 126 so that the results look more like this:

SELECT CAST(GETDATE() AS VARCHAR)
-- 2015-05-08T09:14:19.437

If possible, I would like to only change the default style for the current process or statement; however, I am also willing to change it at the database or schema level.

I should note: I am aware of CONVERT and the ability to manually specify the style. However, in this particular situation, I am unable to use CONVERT. I am CASTing a SQL_VARIANT parameter to a scalar-valued function as VARCHAR.

Answer 1

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.

Answer 2

You can use

SET DATEFORMAT dmy

visit https://msdn.microsoft.com/en-us/library/ms189491.aspx for more details.

Or you can specify type with convert, visit http://www.sql-server-helper.com/tips/date-formats.aspx for more details.

Hope that helps.

Regards,

Maulin.