CODEWITHSUNDEEP
sqlsql-serversql-server-2008datetimesql-server-2008-r2
Michael Durrant
Michael Durrant

Reputation: 96454

SQL Server - how can I select date-hour from a date-time field

How can I do the oracle equivalent of:

to_char(date_field, 'YYYY-MM-DD:HH')  # e.g. 2012-05-25:19

in SQL Server ?

I want the column for ordering which is why I want year-month-day-hour

Upvotes: 3

Views: 16500

Answers (3)

Michal Tsadok
Michal Tsadok

Reputation: 153

For SQL Server 2012 and up:

 SELECT FORMAT(GETDATE(),'yyyy-MM-dd:HH')

Upvotes: 2

Jon Egerton
Jon Egerton

Reputation: 41539

Unfortunately mssql isn't great at custom date formats. You're stuck with string parsing:

e.g.

select replace(CONVERT(varchar(13),date_field,121),' ',':')

The full details of the formats that are available are here: http://msdn.microsoft.com/en-us/library/ms187928.aspx

Note: If you're lucky enough to be in SQL 2012 you get a new FORMAT function, which is basically a wrapper of the .Net equivalent: See here: http://msdn.microsoft.com/en-us/library/hh213505.aspx

Upvotes: 3

anouar.bagari
anouar.bagari

Reputation: 2104

select convert(varchar(10),date_field,120) + ':'+
convert(varchar(2), datepart(hour,date_field))

Upvotes: 6

Related Questions