| Author |
Topic |
|
sanjay5219
Posting Yak Master
240 Posts |
 Posted - 2009-09-25 : 10:38:48
|
| Dear All,I haev one table in which i am storing data.Now ihave to add 12 HRs in start_time and in End_Time .Can u please help Start_Time Status End_Time10:34 AM NULL 08:00 PM 03:56 PM NULL 09:10 PM 10:51 AM NULL 03:55 PM 10:58 AM NULL 08:00 PM 07:55 AM NULL 07:55 AM 07:49 AM NULL 05:06 PM 09:57 AM NULL 12:43 PM 02:16 PM NULL 07:13 PM 12:53 PM NULL 02:16 PM |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2009-09-25 : 11:08:50
|
| you mean:DATEADD(hour, 12, Start_time) as StartTimePlus12Be One with the OptimizerTG |
 |
|
|
sanjay5219
Posting Yak Master
240 Posts |
Posted - 2009-09-25 : 11:17:00
|
| Yes but it will change PM AM also |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2009-09-25 : 11:47:54
|
AM/PM is just a formatting consideration - it doesn't affect the datetime value returned by DATEADD. select convert(varchar, d.dt, 121) [24hour] ,convert(varchar, d.dt, 109) [12hour] ,convert(varchar, d.dt, 101) [justDate]from ( select getdate() as [dt] union all select dateadd(hour, 12, getdate()) union all select dateadd(hour, -12, getdate()) ) dOUTPUT:24hour 12hour justDate------------------------------ ------------------------------ ------------------------------2009-09-25 11:45:26.410 Sep 25 2009 11:45:26:410AM 09/25/20092009-09-25 23:45:26.410 Sep 25 2009 11:45:26:410PM 09/25/20092009-09-24 23:45:26.410 Sep 24 2009 11:45:26:410PM 09/24/2009 Be One with the OptimizerTG |
|
|
|
sanjay5219
Posting Yak Master
240 Posts |
Posted - 2009-09-25 : 12:46:35
|
| But if we take this example how it os going to help me.If i have 08:00 AM and i will write DATEADD(hh, 12, 08:00 AM) as StartTimePlus12will it give me result as 08:00 PM |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2009-09-25 : 13:09:08
|
| Sorry, I must have misunderstood what you're asking. I thought you wanted to know how to add 12 hours to a datetime value.Post what values you are starting with and post what values you want to end up with based on the starting values.And please include what DATATYPE your columns are.Be One with the OptimizerTG |
|
|
|
sanjay5219
Posting Yak Master
240 Posts |
Posted - 2009-09-25 : 13:27:22
|
| I will make it simple i have US Time (10:34 AM) and i have to convert this to manila time which should be 10:34 PM (Add 12 Hr) |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2009-09-25 : 13:54:09
|
I guess it's still not simple enough for me because it really sounds like "how do I add 12 hours to a datetime value?"Now I'm really curious as to our mis-communication because what you say you want to do is exactly what you previously said won't help you. (below) You know you can use a datetime column in place of "08:00 AM" for the DATEADD function, right?quote:
But if we take this example how it os going to help me.If i have 08:00 AM and i will write DATEADD(hh, 12, 08:00 AM) as StartTimePlus12will it give me result as 08:00 PM
Is it because your "US Time" isn't a datetime datatype?I'll re-ask one of my previous questions: what is the datatype of this "US Time (10:34 AM) ?Be One with the OptimizerTG |
|
|
|
sanjay5219
Posting Yak Master
240 Posts |
Posted - 2009-09-25 : 14:07:35
|
| varchar |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-09-25 : 14:16:39
|
| Nope you should use datetime for storing the values and then it will be as easy as what TG suggests. When you look at table, you will find date values also stored along with time (like 1900-01-01 08:00:00) but it wont affect you as you can always use format functions at front end to show only time or use CONVERT(varchar(8),datefield,108) in t-sql |
|
|
|
sanjay5219
Posting Yak Master
240 Posts |
Posted - 2009-09-25 : 14:18:54
|
| great |
|
|
|
|
Add Time