3

In SQL Server, can I do the equivalent of to_char(date_field, 'YYYY-MM-DD:HH24') in Oracle to get 2012-01-30:23?

1
  • 2
    Check out Aaron Bertrand's answer. It's more complete than mine.
    – squillman
    Jun 27, 2012 at 15:41

3 Answers 3

3

You can use the CONVERT() function and pass it one of the precanned styles. Looks like style 120 (ODBC Canonical) is the closest to what you specifiy.

Example:

CONVERT(VARCHAR(20), SYSDATETIME(), 120)

Also, have a look at Pinal Dave's UDF here. It's quite a bit more flexible.

6
  • I'm not sure I understand how this produces 2012-01-30:23 - aren't there way too many characters, and no colon between the date and the hour? Jun 27, 2012 at 15:19
  • I'd said it was closest, not exact.
    – squillman
    Jun 27, 2012 at 15:31
  • Right, my concern is that as the accepted answer, other readers are going to come along, grab this "solution" and discover that it actually doesn't work to give the OP the output he asked for. Jun 27, 2012 at 15:32
  • I hear you, which was why I included the verbiage about it being the closest. Yours is definitely more spot on. About it being accepted? Well...
    – squillman
    Jun 27, 2012 at 15:34
  • To be clear, I don't care that it's accepted and mine isn't. The fact that it's accepted just means it's the only one some future readers might look at. I think there are a couple of problems at play here: (1) the OP might have felt this was close enough, and figured out how to go from here (2) OPs are encouraged to vote and accept early. Too early, IMHO. No offense but this demonstrates a case where the first answer was not the best or most correct answer, and the OP may have been better off waiting. In many cases the OP might never get a better answer because accepted Qs are largely ignored. Jun 27, 2012 at 15:50
10

In SQL Server <= 2012 you can say:

SELECT CONVERT(CHAR(10), date_field, 120) 
  + ':' + RIGHT('0' + CONVERT(VARCHAR(2), DATEPART(HOUR, date_field)), 2)
  FROM dbo.table ...;

Or

SELECT STUFF(CONVERT(CHAR(13), date_field, 120), 11, 1, ':')
  FROM dbo.table ...;

In SQL Server 2012 this is much easier; you can say:

SELECT FORMAT(date_field, 'yyyy-MM-dd:HH')
  FROM dbo.table ...;

Though FORMAT is considerably slower, so if you are doing this at scale, it isn't the one I would use (and in fact I would highly recommend you consider formatting your date output at the presentation layer, not in SQL Server).

SQL fiddle: http://sqlfiddle.com/#!6/72276/5

-2

something like

DECLARE @D DATETIME 
SET @D = GETDATE()
SELECT CONVERT(varchar(20), @D , 120) as date
1
  • I'm not sure I understand how this produces 2012-01-30:23 - aren't there way too many characters, and no colon between the date and the hour? How is this different from the other answer that does the same thing? Jun 27, 2012 at 15:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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