• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4437
  • Last Modified:

SQL Server, Convert Military to Standard Time

Here is my current SQL:

SELECT (startDate + ' From ' + startTime+ ' To ' + endTime) AS FullTime, dateTimeKey
FROM DatesTimes
WHERE DatesTimes.reservationKey = 79

startTime and endTime are being stored in the database as Strings.  The hold time values in hh:mm military format.  I would like to pull these values from the database and store them in FullTime but having a standard time format rather than military  (with AM or PM showing).

I tried this, but it didn't work:

SELECT convert(startTime, 108) AS Starting, convert(endTime, 108) AS Ending,
(startDate + ' From ' + Starting + ' To ' + Ending) AS FullTime, dateTimeKey
FROM DatesTimes
WHERE DatesTimes.reservationKey = 79

Any help would be most appreciated.
0
rreister
Asked:
rreister
  • 2
1 Solution
 
muzzy2003Commented:
The first line will pull the start and end times out as genuine date time values. You can't then references these directly though, so the second line might be all you really want. Haven't tested it, so if it's not quite right let me know and I'll tweak.

SELECT CONVERT(datetime, startDate + ' ' + startTime) AS Starting, CONVERT(datetime, startDate + ' ' + endTime) AS Ending,
startDate + ' From ' + RIGHT(CONVERT(varchar(20), CONVERT(datetime, startTime), 0), 7) + ' To ' + RIGHT(CONVERT(varchar(20), CONVERT(datetime, endTime), 0), 7) AS FullTime, dateTimeKey
FROM DatesTimes
WHERE DatesTimes.reservationKey = 79
0
 
rreisterAuthor Commented:
It worked perfectly!  Thanks so much for quick and easy reply!
0
 
muzzy2003Commented:
You're welcome
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now