time function in T-SQL

GETDATE()
Returns a datetime2(7) value that contains the date and time of the computer on which the instance of Server is running. The time zone offset is not included.
SYSDATETIME()
Returns a datetime2(7) value that contains the date and time of the computer on which the instance of SQL Server is running. The time zone offset is not included.

SQL Loader

if table user varchar2, load file need use char

command: sqlldr userid=ora_id/ora_passwd control=control_file_name.ctl

LOAD DATA
INFILE *
INTO TABLE image_table
REPLACE
FIELDS TERMINATED BY ','
(
image_id INTEGER(5),
file_name CHAR(30),
image_data LOBFILE (file_name) TERMINATED BY EOF
)
BEGINDATA
001,image1.gif
002,image2.jpg
003,image3.jpg

Time range selection in T-sql statement

select *
from everts
where [opened Date] between #09-09-2009# and #03-03-2010#

Outer join with oracle SQL

An outer join use a (+) on the side of the operator where we want to have nulls returned if no value matches.
Create table data_usage (
c_time date,
m_time date,
V varchar2(20),
name varchar2(20),
host varchar2(20)
)
Insert into data_usage values (sysdate, sysdate, ‘SSN’, ‘SCOTT’, ‘D1’);
Insert into data_usage values (sysdate, sysdate+1, ‘SSN’, ‘SCOTT’, ‘D1’);
Insert into data_usage values (sysdate, sysdate+2, ‘SSN’, ‘SCOTT’, ‘D1’);
Insert into data_usage values (sysdate, sysdate, ‘SSN’, ‘TOM’, ‘D2’);
Insert into data_usage values (sysdate, sysdate+ 0.5, ‘SSN’, ‘TOM’, ‘D2’);
Insert into data_usage values (sysdate, sysdate+1.5, ‘SSN’, ‘TOM’, ‘D2’);
Insert into data_usage values (sysdate, sysdate, ‘SSN’, ‘MIKE’, ‘D3’);
Insert into data_usage values (sysdate, sysdate, ‘SSN’, ‘DAVID, ‘D3’);
Create table users (
Name varchar2(20),
Host varchar2(20)
)

Insert into users values ( ‘SCOTT’, ‘D1’);
Insert into users values ( ‘TOM’, ‘D2’);
Insert into users values ( ‘SCOTT’, ‘D2’);
Insert into users values ( ‘MIKE’, ‘D3’);
Insert into users values ( ‘DAVID’, ‘D1’);
Insert into users values ( ‘SAM’, ‘D2’);
Insert into users values ( ‘MIKE’, ‘D1’);

If we want rows without null value.

Select u.name, u.host, d.m_time, d.v
From users u, data_usage d
Where u.name = d.name
And u.host = d.host;

NAME HOST M_TIME V
-------------------- -------------------- ----------- --------------------
SCOTT D1 31-AUG-2010 SSN
SCOTT D1 01-SEP-2010 SSN
SCOTT D1 02-SEP-2010 SSN
TOM D2 31-AUG-2010 SSN
TOM D2 31-AUG-2010 SSN
TOM D2 01-SEP-2010 SSN
MIKE D3 31-AUG-2010 SSN

If we want null for users table, both u.name and u.host need associate with an (+).
Select u.name, u.host, d.m_time, d.v
From users u, data_usage d
Where u.name(+) = d.name
And u.host(+) = d.host
;

NAME HOST M_TIME V
-------------------- -------------------- ----------- --------------------
SCOTT D1 02-SEP-2010 SSN
SCOTT D1 01-SEP-2010 SSN
SCOTT D1 31-AUG-2010 SSN
TOM D2 01-SEP-2010 SSN
TOM D2 31-AUG-2010 SSN
TOM D2 31-AUG-2010 SSN
MIKE D3 31-AUG-2010 SSN
31-AUG-2010 SSN

If we want null for data_usage table.

Select u.name, u.host, d.m_time, d.v
From users u, data_usage d
Where u.name= d.name(+)
And u.host = d.host(+)
;
NAME HOST M_TIME V
-------------------- -------------------- ----------- --------------------
SCOTT D1 31-AUG-2010 SSN
SCOTT D1 01-SEP-2010 SSN
SCOTT D1 02-SEP-2010 SSN
TOM D2 31-AUG-2010 SSN
TOM D2 31-AUG-2010 SSN
TOM D2 01-SEP-2010 SSN
MIKE D3 31-AUG-2010 SSN
SAM D2
MIKE D1
SCOTT D2
DAVID D1

ATTN: (+) can not be added to both side of an relation for outer-joined table

Let’s put date in the select

If we want rows without null value.

Select u.name, u.host, d.m_time, d.v
From users u, data_usage d
Where u.name = d.name
And u.host = d.host
And d.m_time > sysdate + 0.2;

NAME HOST M_TIME V
-------------------- -------------------- ----------- --------------------
SCOTT D1 02-SEP-2010 SSN
SCOTT D1 01-SEP-2010 SSN
TOM D2 01-SEP-2010 SSN
TOM D2 31-AUG-2010 SSN
If we want null for users table, both u.name and u.host need associate with an (+).
Select u.name, u.host, d.m_time, d.v
From users u, data_usage d
Where u.name(+) = d.name
And u.host(+) = d.host
And m_time > sysdate - 1
;

NAME HOST M_TIME V
-------------------- -------------------- ----------- --------------------
SCOTT D1 02-SEP-2010 SSN
SCOTT D1 01-SEP-2010 SSN
SCOTT D1 31-AUG-2010 SSN
TOM D2 01-SEP-2010 SSN
TOM D2 31-AUG-2010 SSN
TOM D2 31-AUG-2010 SSN
MIKE D3 31-AUG-2010 SSN
31-AUG-2010 SSN

If we want null for data_usage table, the m_time need a (+) too.

Select u.name, u.host, d.m_time, d.v
From users u, data_usage d
Where u.name= d.name(+)
And u.host = d.host(+)
And m_time(+) > sysdate
;
NAME HOST M_TIME V
-------------------- -------------------- ----------- --------------------
SCOTT D1 31-AUG-2010 SSN
SCOTT D1 01-SEP-2010 SSN
SCOTT D1 02-SEP-2010 SSN
TOM D2 31-AUG-2010 SSN
TOM D2 31-AUG-2010 SSN
TOM D2 01-SEP-2010 SSN
MIKE D3 31-AUG-2010 SSN
SAM D2
MIKE D1
SCOTT D2
DAVID D1