Hi,
I have a column called begintime and is in string format time like 1545, from a table called "appointments" I need to convert this to 3:45 pm, how would I do this?
Thanks,
Pasi
CAST(STUFF('1545',3,0,':') AS TIME)
This is filler text to get around posting error.
1 Like
Thanks but I need to include the column name as well ? I am using this but not working:
CONVERT(VARCHAR(8), begintime, 100) + ' ' + RIGHT(CONVERT(VARCHAR(30), begintime, 1), 2)
Thaks James but this is what I am getting: I need it like 7:30 am or pm
out put---> 07:30:00.0000000
If you are doing the formatting to store the result into a database table, don't do any further conversions. Store it in a column of type TIME. If you are trying to do the formatting for presentation purposes, best practice is to do the formatting in the presentation layer such as a web page or reporting service.
If you do want to do the formatting in SQL,
CONVERT(VARCHAR(32),CAST(STUFF(begintime, 3, 0, ':') AS TIME),100)
When you do this, you are converting the TIME to a string. So if you needed to do any additional operations, for example sort the results, it will do a string sort rather than a time sort.
1 Like
Thanks that did it! I am trying to build in query to export into presentation layer of application such as crystal report. just out of curiosity, why shouldn't I do formatting in the db?
this is how I did it now: convert(varchar(32), CAST(STUFF(begintime, 3, 0, ':') AS TIME),100) as beginTime
For a variety of reasons:
a) SQL Server loses the ability to do any further manipulations on the data such as sorting.
b) You can lose precision when you format approximate number types (FLOAT)
c) Reporting services usually have much better facilities for formatting. For example, if your requirement called for a space before the AM/PM, it would be hard for SQL to do it. It can be done, but it is just painful. In reporting services it is almost trivial to format it the way you want.
I am sure there are other reasons, but those are a few.
2 Likes
Ok got it. thanks!