-- 전체 테이블에서 특정 단어를 포함하는 이름의 컬럼 찾기
SELECT COLUMN_NAME, TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME LIKE '%shop_id%'
-- 또는
select * from INFORMATION_SCHEMA.COLUMNS
where COLUMN_NAME like '%poi_nm%'
order by TABLE_NAME
-- convert from date to char (yyyy-MM-dd)
select convert(char(10), column_name, 121) from table;
-- convert from char to int
select cast(column_name as int)
from table_name
-- convert from date to int
select cast (convert(varchar(10), getdate(), 112) as int)
from table_name
-- date타입의 값에서 일정 시간 빼기 (start~end day 총 일에 하루를 더한 값이 나온다. 1~5면 6이 나옴)
SELECT GETDATE() 'Now',
DATEADD(minute,-2, GETDATE()) 'Now + 2 Minutes'
ex)
select column_name1,
DATEADD(hour, -1, GETDATE()) 'column_name1 - 1 hours'
from table_name;
-- Subtract between two dates (within 1460 hours since now)
select *
from table_name
where DATEDIFF(hour, reg_tm, GETDATE()) < 1460
-- check null
select title, ISNULL(column_name, 'It's null')
from table_name
-- row_nium (pagination)
select a.row_num
from ( select row_number() over (order by column_name desc) as row_num, *
from tableName
)a
where a.row_num between 1 and 30;
-- select all except for
select *
from table1
where column_name1 not in (select column_name2 from table2)
EmoticonEmoticon