Add 17 hours to a date and time.

building semantic layer
1

I have a date in formatt mm/dd/yyyy and time 0,30,100,130,200…2330
as 30 min increments.I want to concatenate date and time and add 17 hours to it to convert it into sydney date time.The database is IBM informix database.

0 is 12:00AM ,30 is 12:30 AM and so on…

For ex:-1/1/2011 date and 0 is 1/1/2011 00:00:00AM needs to be converted to 1/2/2011 5:00:00 AM.

Any help regarding above is appreciated.

Thanks,
–H

hourigan (BOB member since 2007-04-06)

2

Wouldn’t it be 5:00:00 PM 1/1/2011…u said 17 hrs added to 12 AM? :?

katullus :us: (BOB member since 2009-08-21)

3

I don’t know if IBM Informix syntax is different to SQL Server, but here’s how I did something similar.

DATEADD(hh,11,CALENDAR_DATE)

You may also want to see if there’s a timezone conversion command you could use - this way it will allow for daylight savings (normally).

mjyoungman :australia: (BOB member since 2006-10-06)

4

Yup thats for sql.Same is not working for Informix.
Thanks,
–h

hourigan (BOB member since 2007-04-06)

5

You need the Informix syntax. Here, this should do it;

http://dbaspot.com/forums/informix/390837-informix-how-decrease-30-minutes-current-time.html

katullus :us: (BOB member since 2009-08-21)

6

Yes i did.Not working.

Thanks

hourigan (BOB member since 2007-04-06)

7

something like this:

extend ( date( YOUR_DATE ), year to minute ) + 30 units minute + 17 units hour

the date() is not necessary if using an actual date field…

Patrick McDonough (BOB member since 2002-08-20)

8

you rock buddy.
Looks like its working.
Thanks a lot

hourigan (BOB member since 2007-04-06)