0

I created a SELECT using the following in SQL Server 2012:

SELECT 
CAST(FORMAT(CONVERT(DATETIME, date_time, 127), 'yyyy-MM-ddTHH:mm:ssZ') AS NVARCHAR(20)) TimeStamp,
FROM myTable

This will result in a date formatted like 2019-03-15T13:25:19Z

How can I achieve the same result using SQL Server 2008 R2 or older?

lzdyzydy
  • 69
  • 1
  • 9

2 Answers2

3

You can achieve this far more easily by just using CONVERT:

SELECT CONVERT(varchar(19),GETDATE(),127) + 'Z';

As I mentioned in my comment FORMAT is actually an awful function, it performs terribly. I posted an answer to another question earlier today on just how badly it does compared to a CONVERT. Don't just use this expression on your 2008- servers, replace the FORMAT expression on your 2012+ servers with this one too.

Thom A
  • 88,727
  • 11
  • 45
  • 75
  • I always thought that functions like format, isnull, ... only peformed bad when used in the `where` clause. I learned something important today – GuidoG Mar 19 '19 at 16:30
  • **[`FORMAT()`](https://learn.microsoft.com/en-us/sql/t-sql/functions/format-transact-sql?view=sql-server-2017)** is a _function_ not an _expression. From docs: _Use the FORMAT function for locale-aware formatting of date/time and number values as strings._ – Ilyes Mar 19 '19 at 16:31
  • `ISNULL`, and `FORMAT`, have performance implicates when in the `WHERE` clause as well @GuidoG, but that's due to SARGability. `FORMAT` specifically, however, is just an awful function. not sure how they managed it but it really is a huge performance killer. – Thom A Mar 19 '19 at 16:31
  • @Sami yes, `FORMAT` is a function, but `CONVERT(varchar(19),GETDATE(),127) + 'Z'` is an expression. – Thom A Mar 19 '19 at 16:32
  • I meant the `WHERE` clause, I updated my comment, sorry about that – GuidoG Mar 19 '19 at 16:33
  • @Larnu Nah, they are both functions, while `CONVERT() / CAST()` are functions can convert an _expression_ of one datatype to another. – Ilyes Mar 19 '19 at 16:35
  • @Sami `+ 'Z'` is not a function. within `CONVERT(varchar(19),GETDATE(),127) + 'Z'` there are 2 functions, `CONVERT`, and `GETDATE()`, however, the whole thing is an expression. My post references the expression the OP uses, as simply replacing `FORMAT` with `CONVERT` would not work; the entire expression needs to be changed. – Thom A Mar 19 '19 at 16:42
1

I think this does what you want:

select replace(convert(varchar(255), getdate(), 120), ' ', 'T') + 'Z'

Code 127 returns milliseconds, which you do not seem to want, so 120 seems more appropriate.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786