• 0

Display Time

Question

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.

Recommended Posts

• 0

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

Share on other sites

• 0

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
Share on other sites

• 0

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

Share on other sites

• 0

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)

Share on other sites

• 0

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.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

×   Pasted as rich text.   Paste as plain text instead

Only 75 emoji are allowed.