| Author |
Topic |
|
sonali.paul
Starting Member
3 Posts |
 Posted - 2008-02-26 : 19:38:00
|
| I'm using SQL server and in the database the 'epDischargeTime' is stored as '51600' for example. How I make sense of this and convert it to something like 10:00am or something. I need to find the % of patients that were discharged before 10am. Thanks in advance! |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-02-26 : 21:22:01
|
| What does 51600 mean? Any logical here? |
 |
|
|
sonali.paul
Starting Member
3 Posts |
Posted - 2008-02-26 : 21:33:04
|
| Well, for some reason in the database there are two fields for date and time. The date field is called 'epDischargeDate' for example, 10/09/2004 12:00:00 a.m. (the time proportion of the field is left at 12:00:00 a.m. for each record). As a result there is another field for time, as I have specified above. I'm not sure what '51600' means. Each time field has a different number like that, so I thought there might be some other special way the DB stores time? I thought someone might have seen time stored like this before..? |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-02-26 : 21:36:44
|
| You have to find out. How to convert if don't know th logical? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-02-26 : 23:11:57
|
| just a guess,Is it representing the number of seconds passed after start of the day (12 midnight)?so 51600 will become 51600/(60*60) =1433 hrs which will 2.33 pm |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2008-02-27 : 01:53:39
|
quote:
Originally posted by sonali.paul
Well, for some reason in the database there are two fields for date and time. The date field is called 'epDischargeDate' for example, 10/09/2004 12:00:00 a.m. (the time proportion of the field is left at 12:00:00 a.m. for each record). As a result there is another field for time, as I have specified above. I'm not sure what '51600' means. Each time field has a different number like that, so I thought there might be some other special way the DB stores time? I thought someone might have seen time stored like this before..?
There is no direct answer until you know what the number means forMadhivananFailing to plan is Planning to fail |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-02-27 : 01:59:14
|
No...51600 seconds since midnight is 14 hours, 20 minutes and 0 seconds.2:20PM for our american friends.
E 12°55'05.25"N 56°04'39.16" |
|
|
|
sonali.paul
Starting Member
3 Posts |
Posted - 2008-02-27 : 18:47:17
|
| Visakh, I found out that you're right. Thanks! So I guess the way to convert it into a normal time is by using the following SQL? CONVERT(VARCHAR(5), epDischargeDate + CAST(epDischargeTime + 0.003 AS DECIMAL(15,5))/86400, 114)?If I want to find out the patients that were discharged before 10am how could I do that? |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2008-02-27 : 18:58:44
|
quote:
Originally posted by sonali.paul
Visakh, I found out that you're right. Thanks! So I guess the way to convert it into a normal time is by using the following SQL? CONVERT(VARCHAR(5), epDischargeDate + CAST(epDischargeTime + 0.003 AS DECIMAL(15,5))/86400, 114)?If I want to find out the patients that were discharged before 10am how could I do that?
You are overcomplicating things; this code will do the conversion:dateadd(second,epDischargeTime,epDischargeDate) Example:select DischargeDateTime = dateadd(second,epDischargeTime,epDischargeDate)from ( -- Test Data select epDischargeDate = convert(datetime,'20080227'), epDischargeTime = 51600 ) aResults:DischargeDateTime ------------------------ 2008-02-27 14:20:00.000(1 row(s) affected) CODO ERGO SUM |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-02-27 : 18:59:49
|
2:20PM for our american friends. Peso |
|
|
|
|
Time conversion