[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 323
  • Last Modified:

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

0
r_lynn
Asked:
r_lynn
  • 5
  • 4
1 Solution
 
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
 
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
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
r_lynnAuthor Commented:
The column name is "Time"
0
 
Lee SavidgeCommented:
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:
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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

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