I am not sure if I am using your code correctly?ġ) To see my example work with you data, I think you could run this query, but it just shows you how it would work. I inputted it like this:SELECT a.first_name, a.last_name,cast(a.date_of_birth as datetime) as DOB, cast( b.appt_date as datetime) as ApptDate,b.begintime, c.location_name, d.description, e.eventFROM person aJOIN appointments b ON a.person_id = b.person_idJOIN provider_mstr d ON b.rendering_provider_id = d.provider_idJOIN location_mstr c ON b.location_id = c.location_idJOIN events e ON b.event_id = e.event_idselect b.begintime, b.begintime = convert(datetime,stuff(b.begintime,3,0,':'))from ( - Test Data select b.begintime = '0000' union all ) bIt tells me their is an error near "=". You can then use front-end formatting in Crystal to display it the way you want. If your time is stored as a 4 character string in format HHMM, this shows how to convert it to a datetime. Could someone tell me where to insert this code to make this conversion? Thanks so much in advance!! :) I tried declaring the variable at the beginning of the query and then adding the conversion to the end but it said to declare so I must be using it wrong. Here is my code:SELECT a.first_name, a.last_name,cast(a.date_of_birth as datetime) as DOB, cast( b.appt_date as datetime) as ApptDate,b.begintime, c.location_name, d.description, e.eventFROM person aJOIN appointments b ON a.person_id = b.person_idJOIN provider_mstr d ON b.rendering_provider_id = d.provider_idJOIN location_mstr c ON b.location_id = c.location_idJOIN events e ON b.event_id = e.event_idIn the beginning the c.begintime is the time field that I need to convert. I am just writing the query through the sql command builder in crystal. I want to try that out but I put it into my code and it told me I had to declare I am not using a stored procedure for this as I don't have access to SQL. You should add another condition if you willhave any midnight times.DECLARE TABLE(MT CHAR(4))SELECT CASE WHEN Hour24 > 12 THEN CONVERT(varchar, Hour24 % 12) + ':' + Minute + ' P.M.' WHEN Hour24 = 12 THEN CONVERT(varchar, Hour24) + ':' + Minute + ' P.M.' ELSE CONVERT(varchar, Hour24) + ':' + Minute + ' A.M.'ĞNDFROM( SELECT CAST(LEFT(MT, 2) AS INT) AS Hour24, RIGHT(MT, 2) AS MinuteğROM a If you just want to return a string you could do something like this. There is no date in the field it is just a time so for example it reads 1410 and I want it to say 2:10 P.M. Everytime I try to use any type of datetime convert it tells me that the conversion of a char data type to a datetime data type resulted in an out of range datetime value. Is their a cast or convert or some type of sql function I can use to select the appointment time in standard time format? I have been searching online and just haven't found anything yet.Thanks in advance!SherriĪnd the military time format would be?you can always do:select convert(datetime, yourDateColumn)from yourTableand then format that datetime in whatever way you want in the report_Causing trouble since 1980blog: Add-in that does a few things: sross81 I am doing this report in crystal and I tried to use crystal tools but it crystal recognizes it as a string so it won't let me set the time format. Hello,I have a database field that is just an appointment time but it is stored as a char and it appears to be in military time as well.For reporting purposes I am creating a query that will use the appointment time and I need it to display in standard time format. We've got lots of great SQL ServerĮxperts to answer whatever question you can come up with.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |