how to get GMT Date time using DB date time.

[Former Member]

Post Author: basham

CA Forum: WebIntelligence Reporting

i need to show date time in GMT in one of my reports, also the time end with 'GMT' and not with 'PM' or 'AM'. Can i do some thing in universe or in DB.

[Former Member]

Post Author: basham

CA Forum: WebIntelligence Reporting

Hey John,

Thanks for your reply.i'm i'm using Oracle 10g DB.Will do the things.

[Former Member]

Post Author: jsanzone

CA Forum: WebIntelligence Reporting

basham,

You didn't mention which type of DBMS you are using (i.e. Oracle, MS SQL, MySQL, etc), but in a nutshell using MS SQL here is the principle. Your time that is recorded in the records is dependent upon a setting in your RDBMS. For instance, the RDBMS can use the machine time (which would most likely be local time), or it can use the machine time with an offset (to accomodate GMT, for instance). GMT is factored differently based upon your time zone (and time of year, i.e. DST or EST, etc). I'm on the east coast so right now my offset is GMT - 5, so if my time right now is 1457, then GMT is 1957. To get SQL to compute an offset you must take the number of hours to offset and divide by 24 (24 hours in a day) and add that to the record in the database. For instance, to get GMT right now I would use:

select cast(getdate()5/24. as char(12))rtrim(convert(char(12),getdate()5/24.,8))' GMT'

Hope this helps. If you're on Oracle, then the mathematical principles remain the same, just the formatting will be different.