CODEWITHSUNDEEP
sqlsql-server-2008
Gestef
Gestef

Reputation: 128

SQL Server : converting time in hh:mm am/pm format

I want to display my results with a hh:mm am/pm format however my query below is only working for the am results but not time from 12:00pm and on

select 
    timein, timeout, 
    SUBSTRING(convert(varchar, timein, 108), 1, 5),  
    SUBSTRING(convert(varchar, TimeOut, 108), 1, 5)
from 
    timeinandoutTable 
where 
    progtype = 'cmp' 
    and TimeIn is not null and TimeOut is not null

Results

Timein                  TimeOut            timeinresult  timeoutresult 
-----------------------------------------------------------------------
1900-01-01 15:00:00     1900-01-01 16:10:00    15:00        16:10
1900-01-01 10:00:00     1900-01-01 17:00:00    10:00        17:00
1900-01-01 09:30:00     1900-01-01 16:00:00    09:30        16:00

Upvotes: 0

Views: 3834

Answers (1)

SlimsGhost
SlimsGhost

Reputation: 2909

You must use one of the styles that uses "AM/PM". Your example (108) DOES NOT. Use something like this instead (style 100, take the last 7 chars):

select timein, timeout, 
substring(convert(varchar(19), timein, 100), len(convert(varchar(19), timein, 100)) - 6, 7), 
substring(convert(varchar(19), timeout, 100), len(convert(varchar(19), timeout, 100)) - 6, 7)
from timeinandoutTable 
where progtype= 'cmp' and TimeIn is not null and TimeOut is not null

Here is a self-contained query you can run to see this working:

select convert(datetime, 'Dec 31 2015 10:25PM', 100) as datetime_value, 
substring(convert(varchar(19), 'Dec 31 2015 10:25PM', 100), len(convert(varchar(19), 'Dec 31 2015 10:25PM', 100)) - 6, 7) as my_string_value


datetime_value         | my_string_value
2015-12-31 22:25:00.000| 10:25PM

Upvotes: 1

Related Questions