SQL Time Format

OK, I am banging my head on something which should be simple!
I am creating a SQL statement to display information from an appointments database.  The data fiels I am reading is formatted 'yyyy-mm-dd hh:mm:sc.ms" (1900-01-01 09:30:00.000).  The date extraction was easy enough for me but the time format I need is eluding me.  The time needs to be formatted 'hh:mm AM(or PM)'


SELECT        PatientID, DoctorID, OfficeID, CONVERT(varchar(10),Date,101)AS Date, time, DurationInMinutes, Cancelled, NewPatientLast, NewPatientFirst, NewPatientMI, Comments
FROM            Appointments
WHERE        (Date = CONVERT(DATETIME, '2011-09-30', 102)) AND (PatientID > 0)
ORDER BY OfficeID 

-------------------------------------------------------------
RESULTS ARE:
PatientID	DoctorID	OfficeID	Date	time	DurationInMinutes	Cancelled	NewPatientLast	NewPatientFirst	NewPatientMI	Comments
75044	5	1	09/30/2011	1900-01-01 07:55:00.000	5	0	NURSE                     	NULL	NULL	NO LASER HAIR IN SEPT

Open in new window

r_lynnAsked:
Who is Participating?
 
Lee SavidgeConnect With a Mentor Commented:
SELECT        PatientID, DoctorID, OfficeID, CONVERT(varchar(10),Date,101)AS Date, substring(convert(varchar(20), [time], 9), 13, 5) + ' ' + substring(convert(varchar(30), [time], 9), 25, 2) as time, DurationInMinutes, Cancelled, NewPatientLast, NewPatientFirst, NewPatientMI, Comments
FROM            Appointments
WHERE        (Date = CONVERT(DATETIME, '2011-09-30', 102)) AND (PatientID > 0)
ORDER BY OfficeID
0
 
Lee SavidgeCommented:
select substring(convert(varchar(20), GetDate(), 9), 13, 5) + ' ' + substring(convert(varchar(30), GetDate(), 9), 25, 2)

gets you the time in AM/PM rather than 24hour.
0
 
r_lynnAuthor Commented:
Cool, this does get me the correct format which is a big part of my issue, but it is grabbibg the system date.  I replace sysdate with the name of the field 'Time' and it fails with
Msg 195, Level 15, State 10, Line 2
'time' is not a recognized built-in function name.
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
Lee SavidgeCommented:
Whatever you replace the getdate() with should be your column name where the datetime is. What is the column name that contains the datetime from which you want the date?
0
 
r_lynnAuthor Commented:
The column name is "Time"
0
 
Lee SavidgeCommented:
Personally I wouldn't use reserved words like date as a column name, but that is my personal preference.
0
 
r_lynnAuthor Commented:
Thanks for your help, my experience , as you can tell, is a bit weak and I have spent some time trying to resolve this knowing that it would be simple for someone with experience.

Thanks Again,
-Randy
0
 
Lee SavidgeCommented:
You're very welcome :)
0
 
r_lynnAuthor Commented:
I agree on the reserved word, but this is what I inherited, thanks again!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.