Tip1: Extracting month from date as 'MMM'
select current_date (format 'MMM');
Date
----
Oct
Please use " sel cast ((cast(current_date as format 'mmm')) as char(3)); " while querying on Sql Assistant(queryman)
Tip2: Converting number of seconds to Minutes and Seconds
If i want to convert the input of seconds into minutes+seconds, following query is used
ex: 300 seconds --> 0005:00 or 5:00 mins
create table table1
( second_s integer );
insert into table1 (300);
Select second_s * INTERVAL '0000:01' MINUTE TO SECOND from Table1;
Please note that ,if seconds interval is more then 9999(approx 7 days) , it will probably result in a "7453: Interval field overflow".
Tip3: Converting number of seconds to Minutes and Seconds
Similarly , We can convert seconds to Day to second format as well!!
Select second_s* INTERVAL '0000 00:00:01' DAY TO SECOND from table1;
This will work for up to 27 years after which it results in "7453: Interval field overflow" error.
Tip4: How to add/subtract minutes to timestamp!!
Problem: To the given timestamp value, how can we add or subtract given minutes.
ex:
Timestamp1 Minutes1 Result
2010-01-04 02:10:08 (+) 330 = 2010-01-04 07:40:08
2010-01-02 18:04:32 (-) 245 = 2010-01-02 13:59:32
create table Table_1
(Timestamp1 timestamp(0) ,Minutes1 integer );
insert into table_1 values('2010-01-04 02:10:08',330);
insert into table_1 values('2010-01-02 18:04:32',245);
select Timestamp1 + (Minutes1 * interval '1' minute) from table_1;
select Timestamp1 - (Minutes1 * interval '1' minute) from table_1;
This post is very useful. I was looking for these calculations since a long time and nothing was as easy as this one. Thanks !!
ReplyDelete