asked on 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)'
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
Microsoft SQL ServerMicrosoft SQL Server 2008
Last Comment
r_lynn
ASKER
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.
Msg 195, Level 15, State 10, Line 2
'time' is not a recognized built-in function name.
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?
ASKER
The column name is "Time"
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Personally I wouldn't use reserved words like date as a column name, but that is my personal preference.
ASKER
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
Thanks Again,
-Randy
You're very welcome :)
ASKER
I agree on the reserved word, but this is what I inherited, thanks again!
gets you the time in AM/PM rather than 24hour.