| Author |
Topic |
|
jonasdavedomingo
Starting Member
33 Posts |
 Posted - 2004-09-22 : 09:41:29
|
Let's say i have a table named tblDate and has 2 columns: date_FirstDate and date_Hours.im asking how do u convert number of hours to a date format in sql query?Let's say the value of date_FirstDate is 09-21-2004 09:22:54 AM and the value of date_Hours is 72.what i want is to convert the date_FirstDate into number of hours then add it to date_Hours. and then i want to convert back the total of hours to date format.How will i do that in sql query statement? i know this is possible in sql coz getting the difference between two dates can be done in sql: DATEDIFF(date1, date2, hh) btw, what is the code if u want to get the sum of two dates? is it DATESUM(date1, date2, dd) ? |
|
|
samsekar
Constraint Violating Yak Guru
437 Posts |
Posted - 2004-09-22 : 09:51:52
|
| Select DATEADD(hh, date_Hours, date_FirstDate) from tablename- Sekar |
 |
|
|
jonasdavedomingo
Starting Member
33 Posts |
Posted - 2004-09-22 : 20:53:59
|
| u didnot get what i mean. im not adding two number of hours, but im adding a date format plus the number of hours.the code u gave me is adding two number of hours and it will convert to date. |
|
|
|
jonasdavedomingo
Starting Member
33 Posts |
Posted - 2004-09-22 : 21:32:55
|
iv tried it but it doesnt work.ok for a clearer view of what i want to happen, here's an example below:I have a table named tblDate that has 2 columns: column_DateReported (data type char) and column_Hour data type int.Let's say tblDate has 1 row:column_DateReported_________column_Hour09-23-2004 09:32:55 AM_____________72what i want to happen is to convert column_DateReported to number of hours then add it to column_Hour which is 72 then get the total then convert the total to date format 09-26-2004 09:32:55 AM.or you can do the other way around, convert the column_Hour 72 to date format then add it to column_DateReported.72 hours is equivalent to 3 days, so if u add the two, you'll get 09-26-2004 09:32:55 AMand if i changed 72 to 10 hours then i should get 09-23-2004 07:32:55 PMIv tried 1.) SELECT DATEADD(hh, column_DateReported, column_Hour) AS totaldate FROM tblDate but this code is adding two numbers where column_DateReported and column_Hour's data type must be int.2.) SELECT (column_DateReported + 72/24) AS totaldate FROM tblDate this doesn't work, null pointer error. |
|
|
|
GunZ
Starting Member
29 Posts |
Posted - 2004-09-22 : 22:06:24
|
quote:
Originally posted by jonasdavedomingo
iv tried it but it doesnt work.ok for a clearer view of what i want to happen, here's an example below:I have a table named tblDate that has 2 columns: column_DateReported (data type char) and column_Hour data type int.Let's say tblDate has 1 row:column_DateReported_________column_Hour09-23-2004 09:32:55 AM_____________72what i want to happen is to convert column_DateReported to number of hours then add it to column_Hour which is 72 then get the total then convert the total to date format 09-26-2004 09:32:55 AM.or you can do the other way around, convert the column_Hour 72 to date format then add it to column_DateReported.72 hours is equivalent to 3 days, so if u add the two, you'll get 09-26-2004 09:32:55 AMand if i changed 72 to 10 hours then i should get 09-23-2004 07:32:55 PMIv tried 1.) SELECT DATEADD(hh, column_DateReported, column_Hour) AS totaldate FROM tblDate but this code is adding two numbers where column_DateReported and column_Hour's data type must be int.2.) SELECT (column_DateReported + 72/24) AS totaldate FROM tblDate this doesn't work, null pointer error.
Mabuhey !!!  I have the impression you're doing some sort of incident logging with measuring the turn around time of 72 hours or less.First off, the column DateReported should be declared datetime so you can operate on the computation with minimal effort in this mannerPRINT 'Today''s date: '+CONVERT(varchar(25),getdate())PRINT 'Today''s date + 72 hours: '+CONVERT(varchar(25),DATEADD(hh,72,getdate())) According to M$ SQL Server 2000 Books Onlinequote:
Microsoft® SQL Server™ 2000 has the datetime and smalldatetime data types to store date and time data.There are no separate time and date data types for storing only times or only dates. If only a time is specified when setting a datetime or smalldatetime value, the date defaults to January 1, 1900. If only a date is specified, the time defaults to 12:00 A.M. (Midnight).
Second, please note that this is a forum; kindly complete your words and to make your posts more English like and avoid posting using abbreviations as if you are sending SMS. English is has lost it's former glamour due to progress.Mabuhey !!! EDIT: Cookie errors... Original post incorrect... edited...Australia.NSW.Sydney.GunZ |
|
|
|
jonasdavedomingo
Starting Member
33 Posts |
Posted - 2004-09-23 : 00:09:50
|
Yahoooo........thanks Gunz, now it works. thanks so much!!!God Bless yah!btw, it is spelled Mabuhay, not Mabuhey! hehehehe!Mabuhay gunz!  |
|
|
|
GunZ
Starting Member
29 Posts |
Posted - 2004-09-23 : 00:16:07
|
Whooops... wrong word (Mabuhey) sorry Quoting myself:quote:
"...the column DateReported should be declared datetime"
and not char...To illustrate what I said, copy and paste the sample code I made on Query Analyzer.In your case, the select statement should be: SELECT DATEADD(hh, column_Hour, column_DateReported) AS totaldate FROM tblDate and to make it work, you must declare column_DateReported as datetimeAustralia.NSW.Sydney.GunZ |
|
|
|
jonasdavedomingo
Starting Member
33 Posts |
Posted - 2004-09-23 : 01:08:23
|
| you can also use char as the data type of column_DateReported.I have tried char and it works.thanks Gunz! |
|
|
|
GunZ
Starting Member
29 Posts |
Posted - 2004-09-23 : 01:45:09
|
 Remember client/server principles... while it's alright to represent dates in char format(as they get implicit conversions), they may occupy larger space than necessary, thus affecting efficiency (plus the fact that future datetime operations might not work right due to illegal characters in the char field). If you need character formatting as you have illustrated, do it on the front end and not on the SQL Server |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-09-23 : 04:36:36
|
| Quick: What date is 11/12/04? Answer: Well ... it depends ...On a Japanesse server it would be December 4th, 2011The only thing I'm pretty confident it can never be is some date in April!!Much better to use datetime format and make sure you pass data/time datatypes around, rather than string/text ones.Kristen |
|
|
|
|
How to convert number of hours to date format?