Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 2 down vote favorite

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?

share|improve this question
2  
Check out Aaron Bertrand's answer. It's more complete than mine. – squillman Jun 27 '12 at 15:41

3 Answers

up vote 4 down vote accepted

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.

share|improve this answer
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? – Aaron Bertrand Jun 27 '12 at 15:19
I'd said it was closest, not exact. – squillman Jun 27 '12 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. – Aaron Bertrand Jun 27 '12 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 '12 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. – Aaron Bertrand Jun 27 '12 at 15:50
show 1 more comment
up vote 7 down vote

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

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

share|improve this answer
up vote 0 down vote

something like 

DECLARE @D DATETIME 
SET @D = GETDATE()
SELECT CONVERT(varchar(20), @D , 120) as date
share|improve this answer
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? – Aaron Bertrand Jun 27 '12 at 15:20
This does not provide an answer to the question. To critique or request clarification from an author, leave a comment below their post. – Max Vernon Oct 16 '12 at 13:41

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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