Differences between databases and their usage (case and time functions)

1. Case
1). SQL Server
Select region_id,
Hehe = case
When region_id <3 then 'yes'
When region_id between 3 and 4 then 'ss'
Else 'no'
End

From DBO. "countries"
Attention: between A and B ==== "> = A and <= B"

2). Oracle
Select job_id, max_salary,
Decode (max_salary, 6000, 'aaa', 40000, 'ggg ', 'ss') operationname
From HR. Jobs
======Or ================================

Select job_id, max_salary,
(Case
When max_salary <6000 then 'aaa'
When max_salary between 6000 and 40000 then 'ggg'
Else 'ss'
End) as jhh
From HR. Jobs

3). MySQL
Select maxlen,
(Case
When maxlen <2 then '000'
When maxlen between 2 and 3 then '123'
Else 'ppp'
End
) As SSS
From character_sets

4). DB2
Select
Customer_id,
(Case
When customer_id <26 then 'llll'
When customer_id between 26 and 33 then 'ppppp'
Else 'kkkk'
End
)
From orders. fortable

5). Derby
Select category_id,
(Case
When category_id <2 then 'oooo'
When category_id between 3 and 7 then 'oooo'
Else 'aaa'
End) as ppoo
From app. Categories

6). Sybase
Select ID,
(Case
When id <2 then 'qqq'
When ID between 2 and 3 then 'uuu'
Else 'ddddd'
End) as iodsioi
From DBO. spt_limit_types

7). Informix
Select category_id,
(Case
When category_id <3 then 'uuui'
When category_id between 3 and 5 then 'ioioo'
Else 'yyy'
End
)
From Informix. Categories

8). Access
Case is not supported

2. year, month, quarter, week
-- Oracle
Select to_number (to_char (sysdate, 'yyyy') Year,
To_number (to_char (sysdate, 'q') quarter,
To_number (to_char (sysdate, 'mm') month,
To_number (to_char (sysdate, 'ww ') Week,
Sysdate
From dual;
Result:
Year quarter month week sysdate
2006 3 8 35 2006-08-28 20:13:27. 0

-- Sybase
/**
Select datepart (year, T. starttime) Year,
Datepart (quarter, T. starttime) quarter,
Datepart (month, T. starttime) month,
Datepart (Week, T. starttime) Week,
T. starttime
From DBO. sysengines t
Result:
Year quarter month week sysdate
2006 3 8 34 2006-08-26 20:13:27. 0

(3.1) SQL Server 2000
Select datepart (year, T. crdate) Year,
Datepart (quarter, T. crdate) quarter,
Datepart (month, T. crdate) month,
Datepart (Week, T. crdate) Week,
T. crdate
From DBO. sysobjects t
Result:
Year quarter month week sysdate
2000 3 8 33 2000-08-6 20:13:27. 0

(3.2) SQL Server 2005
Select datepart (year, T. crdate) Year,
Datepart (quarter, T. crdate) quarter,
Datepart (month, T. crdate) month,
Datepart (Week, T. crdate) Week,
T. crdate
From DBO. sysobjects t
Result:
Year quarter month week sysdate
2005 4 10 42 20:13:27. 0

4) DB2
Select Year (current timestamp) yaer,
Quarter (current timestamp) yaer,
Month (current timestamp) yaer,
Week (current timestamp) yaer,
Current Timestamp
From sysibm. sysdummy1
Result:
Year quarter month week sysdate
2006 3 8 35 2006-08-28 20:13:27. 0

5) MySQL
Select Year (now () yaer,
Quarter (now () yaer,
Month (now () yaer,
Week (now () yaer,
Now ()
Result:
Year quarter month week sysdate
2006 3 8 35 2006-08-28 20:13:27. 0

Quarter, week not support by Derby
6) Derby
Select Year (current timestamp) yaer,
(Month (current timestamp) + 2)/3 quarter,
-- Quarter (current timestamp) yaer, quarter not support by Derby
Month (current timestamp) yaer,
-- Week (current timestamp) yaer, week not support by Derby
Current Timestamp
From sysibm. sysdummy1
Result:
Year quarter month week sysdate
2006 3 8 35 2006-08-28 20:13:27. 0

Quarter, week not support by Informix
7) Informix
Select Year (current) Year,
Round (month (current)-2)/3 + 1 ),
-- Quarter (current) month,
Month (current) month,
-- Week (current) month
Current
From Informix. Categories
Result:
Year quarter month sysdate
2006 3 8 2006-08-28 20:13:27. 0

Quarter, week not support by Access
8) Access
Select orderdate,
Year (orderdate) as 'Year ',
INT (orderdate + 2)/3) as 'quarter ',
Month (orderdate) as 'month'
From orders
PS: The result return the "month" from 1-12.