Display Time

[taylorswiftlover]

Is there a way to use DatePart in a calculated field to show time only? I've been trying to use various DatePart functions but I cannot display the time only. 

[SinJunYoung]

You need to use convert instead of DatePart and use 108 or 114 as the format number. The formula should be: CONVERT(DATETIME, [@field:], 114). 

[SinJunYoung]

For Date Conversion format, here's a helpful table for reference:

 

#	Format
101	mm/dd/yyyy
102	yyyy.mm.dd
103	dd/mm/yyyy
104	dd.mm.yyyy
105	dd-mm-yyyy
108/114	To get the time
110	mm-dd-yyyy
111	yyyy/mm/dd
112	yyyymmdd
126	yyyy-mm-dd

[DesiLogi]

A couple years later--just want to say this is perfect and really helpful- thanks for posting it. 

[RuisiHansamu]

Hey there, you might want to use or check this link as well for future references: https://www.mssqltips.com/sqlservertip/1145/date-and-time-conversions-using-sql-server/ 

[Queso]

I did this on one of my Tabular Report's Calculated Field. I separated them with colons so I was required to convert those DateParts into text using CAST function because it is being rendered as integer:

CAST(DatePart(hour, [@field:Timestamp]) as VARCHAR) + ':' + CAST(DatePart(minute, [@field:Timestamp]) as VARCHAR) +':'+ CAST(DatePart(second, [@field:Timestamp]) as VARCHAR)

Above formula is too long.. the simplest way is to use CONVERT to get its time:

CONVERT(varchar(10), [@field:Timestamp], 114)

[PotatoMato]

Hi! Just to add, you may check this link for different Date formats on SQL: https://www.sqlshack.com/sql-convert-date-functions-and-formats/

[Kurumi]

Hi - I would like to add on this post. In case, you would like to get the month using DATEPART() and add leading zero, you may use these formulas:

RIGHT('0' + CAST(DATEPART(month, [@field:DATE]) AS VARCHAR(10)), 2)

or 

RIGHT('0' + CONVERT(VARCHAR(2), DATEPART(MM, [@field:DATE])), 2)

Result:

January = 1 to 01
February = 2 to 02

Since the DATEPART() function returns an integer, leading zero's will always disappear and need to be manually added like so.

Reference:
https://www.w3schools.com/sql/func_sqlserver_datepart.asp