Seconds Past Midnight
SQL Server conversion
Recently I was tasked with creating an automatic email appointment reminder system for a healthcare practice. While building the app, I discovered that the database stores it's appointment time values as an integer, calculated as "seconds past midnight". So for example 1:30PM get stored as 48600. I needed to convert this integer to a readable time format. I could do this in Flex or ColdFusion as a separate calculation function, but discovered that SQL Server could do this for me on the fly right in the SELECT statement using the DATEADD() function. Here is the code in ColdFusion:
<!---Set todays date--->
<cfparam name="TodayDate" default="#DateFormat(Now(), "mm/dd/yyyy")#">
<!---Query Database using DATEADD() function and alias the field as DBApptTime--->
<cfquery datasource="YourDSN" name="test">
SELECT DATEADD(s,ApptTimeFieldName,#TodaysDate#) as DBApptTime
FROM AppointmentTable
</cfquery>
<!---Comes back as "Year-Month-Day Hour:Minute:Second:Millisecond" format--->
<cfset displayAppointmentTime = #TimeFormat(DBApptTime, "hh:mm tt")#>
<!---TimeFormat CF function removes the "Year-Month-Day" part, and formats the time to a common display with AM/PM. Note: Use capitol HH:MM for military time display--->
<cfoutput>#displayAppointmentTime#</cfoutput>
Works like a charm everytime!
-Christopher Keeler
You are not logged in, so your subscription status for this entry is unknown. You can login or register here.
No comments found.