Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM SQL » View Thread |
Messages 1 to 7 of 7 total |
Update timestamp with percentage |
Wed, Sep 5 2018 6:32 PM | Permanent Link |
Huseyin Aliz myBiss ApS | Hi All,
I have a table x, containing a timestamp field (endtime). I want to update endtime with percentage, like minus 10 procent, 12,5 procent etc (float) for all records.. Something like: update x set endtime = endtime - ocent Tried several casting options, but did not succeed yet. Any ideas how this can be solved? Thanks in advance Regards, Hüseyin |
Wed, Sep 5 2018 6:44 PM | Permanent Link |
Fernando Dias Team Elevate | Hüseyin,
A percentage of what? Can you give us an example with real values and what would you expect as a result? -- Fernando Dias [Team Elevate] |
Thu, Sep 6 2018 2:27 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Hüseyin
I've just tried here with my old DBSys 4.25b5 and I can only CAST a timestamp into a DATE, TIME or VARCHAR and the only calculations I can carry out are + and - of integers (milliseconds). However, as Fernando asks - a percentage of what? The restrictions above make sense since TIMESTAMP, DATE, TIME are simply markers. If, as I guess, you're using it to show elapsed time or duration then you are going to have problems Roy Lambert |
Thu, Sep 6 2018 3:30 AM | Permanent Link |
Huseyin Aliz myBiss ApS | Hi Fernando/Roy,
Sorry not being clear. Let me see if i can explain better with some examples.. I have starttime and endtime with following data: Starttime                  Endtime 04-09-2018 10:00:00   04-09-2018 20:00:00 05-09-2018 12:00:00   05-09-2018 22:00:00 Endtime - starttime = app. 10 hours. Let's say that i want to update endtime with 25% less. The results should be: Starttime                  Endtime 04-09-2018 10:00:00   04-09-2018 17:30:00 05-09-2018 12:00:00   05-09-2018 19:30:00 Endtime - starttime = app. 7,5 hours. (75% percentage of 10 hours).. The percentage changes for every sql, so it's going to be a parameter in the sql. I hope it's more clear now Thanks. Hüseyin Den 06-09-2018 kl. 08:27 skrev Roy Lambert: > Hüseyin > > > I've just tried here with my old DBSys 4.25b5 and I can only CAST a timestamp into a DATE, TIME or VARCHAR and the only calculations I can carry out are + and - of integers (milliseconds). > > However, as Fernando asks - a percentage of what? The restrictions above make sense since TIMESTAMP, DATE, TIME are simply markers. If, as I guess, you're using it to show elapsed time or duration then you are going to have problems > > Roy Lambert > |
Thu, Sep 6 2018 4:51 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Hüseyin
Try update x set endtime = starttime + CAST((endtime - starttime) * 0.75 AS INTEGER) I don't know if it can be parameterised since I don't have anything to test it on, but if not simply have a placeholder for the percentage and generate the sql for each run Roy Lambert |
Thu, Sep 6 2018 7:02 AM | Permanent Link |
Huseyin Aliz myBiss ApS | Hi Roy,
Thanks, working nice with the sql statement. I just need to get percentage parameterised, then it's done Regards, Hüseyin Den 06-09-2018 kl. 10:51 skrev Roy Lambert: > Hüseyin > > Try > > update x set endtime = starttime + CAST((endtime - starttime) * 0.75 AS INTEGER) > > I don't know if it can be parameterised since I don't have anything to test it on, but if not simply have a placeholder for the percentage and generate the sql for each run > > Roy Lambert > |
Thu, Sep 6 2018 8:26 AM | Permanent Link |
Huseyin Aliz myBiss ApS | Hi,
update x set endtime = starttime + CAST((endtime - starttime) * ((100 - rcentage) / 100) AS INTEGER) works as expected Thanks again for your help. Regards, Hüseyin Den 06-09-2018 kl. 13:02 skrev Hüseyin Aliz: > Hi Roy, > > Thanks, working nice with the sql statement. I just need to get > percentage parameterised, then it's done > > Regards, > Hüseyin > > Den 06-09-2018 kl. 10:51 skrev Roy Lambert: >> Hüseyin >> >> Try >> >> update x set endtime = starttime + CAST((endtime - starttime) * 0.75 >> AS INTEGER) >> >> I don't know if it can be parameterised since I don't have anything >> to test it on, but if not simply have a placeholder for the >> percentage and generate the sql for each run >> >> Roy Lambert >> > |
This web page was last updated on Wednesday, March 27, 2024 at 05:29 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |