5

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.

closed as off-topic by Max Vernon, Philᵀᴹ, dezso, RLF, Mark Sinkinson May 11 '15 at 15:11

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – Max Vernon, Philᵀᴹ, dezso, RLF, Mark Sinkinson
If this question can be reworded to fit the rules in the help center, please edit the question.

  • Do you want something like SELECT CONVERT(VARCHAR(23), GETDATE(), 126) ? – Kin Shah May 8 '15 at 14:55
  • 5
    What on earth does "unable to use CONVERT" mean? – Aaron Bertrand May 8 '15 at 14:57
  • 6
    Also, Please stop using VARCHAR without length! – Kin Shah May 8 '15 at 15:00
  • "Unable to use CONVERT" means that my use case requires that I am able to blindly CAST a SQL_VARIANT variable. I cannot use CONVERT because if I specify a style and then receive anything other than a DATETIME in the SQL_VARIANT variable, it will fail. This is why I need to change the default style, because I'm relying on the automatic conversion behavior of CAST when the SQL_VARIANT variable is indeed a DATETIME. – oconnerj May 8 '15 at 15:37
7

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.

  • Thanks for the answer. I noticed one thing: in both forms of your case statement, you're first converting the value to CHAR(23), then back to DATETIME, then to either CHAR(23) or VARCHAR(4000). This will achieve the style conversion I wanted (i.e. into style 126) but still loses the precision. I removed the innermost CONVERT and it seems to be working fine. – oconnerj May 8 '15 at 20:04
  • @oconnerj The inner CONVERT did not do that in my test IIRC. But in fact the inner CONVERT is likely not necessary in most cases, it could also be left(y,23), but I put it in to guard against the case where the first 23 characters represented a valid datetime and then the following characters were garbage. (In fact you can't protect against this when using GARBAGE_VARIANT.) I'll work up a sample when I'm back at a proper computer. – Aaron Bertrand May 9 '15 at 1:09
0

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.

  • This is not what I need. SET DATEFORMAT only allows me to change the order of the day/month/year. I need to be able to change the default style used by CAST and by CONVERT when a style is not otherwise specified. I am aware of CONVERT, as I said in my original question. – oconnerj May 8 '15 at 15:39
  • How about using convert and cast together? It may slow down your query but will give you a format you need. The way one of our sql server is configured, throws an error if you pass dates in American format 5th of Feb becomes 2nd of May, so I convert it using code 106 and then cast it to date. We don't have enough permission on the server to change the configuration – mouliin May 9 '15 at 18:07

Not the answer you're looking for? Browse other questions tagged or ask your own question.