|
##
#
# PHP
#
#####
# 날짜간의 차이가 몇일인지
$days = (strtotime(date("Y-m-d")) - strtotime("2005-12-31")) / (60 * 60 * 24);
# timestamp -> 날짜
# 날짜 포맷 변경
$date = date("Y-m-d")
$date = date("Y-m-d H:i:s")
$date = date("Y-m-d", $timestamp)
# 날짜 -> timestamp
# outputs: 1136930970
# 날짜 형식이 맞지 않으면 -1 리턴
$timestamp = strtotime('2006-01-10 16:09:30');
# Returns the Unix timestamp
# 현재일에 30일을 더한 날짜
$timestamp = mktime(0,0,0, date("m"), date("d")+30, date("Y"));
$date = date("Y-m-d", $timestamp)
$day = date("Y-m-d", mktime(0,0,0, 12+1, 1, 2005)); // 2006-01-01
$day = date("Y-m-d", mktime(0,0,0, 12+1, 1-1, 2005)); // 2005-12-31
$day = date("Y-m-d", mktime(0,0,0, 12+1, 1, 2005)-86400); // 2005-12-31
$time = time();
date("Y-m-d",strtotime("-1 day", $time));
date("Y-m-d",strtotime("-1 day", $time));
date("Y-m-d",strtotime("now", $time));
date("Y-m-d",strtotime("+1 day", $time));
date("Y-m-d",strtotime("+1 week", $time));
date("Y-m-d",strtotime("-1 month", $time));
date("Y-m-d",strtotime("+1 month", $time));
date("Y-m-d",strtotime("+6 month", $time));
date("Y-m-d",strtotime("+12 month", $time));
date("Y-m-d",strtotime("next Thursday", $time));
date("Y-m-d",strtotime("last Monday", $time));
date("Y-m-d",strtotime("10 September 2000", $time));
strtotime("+5 minutes")
strtotime('first day of')
# 특정String을 변형
strtotime("2008-06-25 23:57:33");
# 현재시간 가져오기
strtotime("now");
# 오늘부터 몇 일전(days, months, years 도 사용가능)
strtotime("-15 days");
# 오늘부터 몇 일후(days, months, years 도 사용가능)
strtotime("+21 days");
# 특정일로 부터 몇일전/후
strtotime("+12 days", strtotime("2008-06-25"));
# 현재 시간 출력
strftime("%Y-%m-%d %H:%M:%S") = 2008-06-25 15:47:58
# 특정일 시간 출력
strftime("%y.%m.%d %r", strtotime("-15 days")) = 08.06.10 03:47:58 PM
# 요일 출력
strftime("%d %b %Y %a") = 25 Jun 2008 Wed
# 요일 출력
strftime("%d %B %Y %A") = 25 June 2008 Wednesday
# 한국어 요일 출력
setlocale(LC_TIME, "ko_KR"); echo strftime("%a %A");
수 수요일
# 한국어 월 출력
setlocale(LC_TIME, "ko_KR"); echo strftime("%b %B");
6월 6월
# 한국어 시간 출력
setlocale(LC_TIME, "ko_KR"); echo strftime("%r");
오후 03시 47분 58초
##
#
# MySQL
#
#####
# 날짜간의 차이가 몇일인지
datediff(b.sel_day, now()) diff
# 날짜 더하기
ADDDATE('2007-03-01',3)
ADDDATE('2007-03-01',INTERVAL 3 MONTH)
YEAR, DAY
# 날짜 포맷 변경
date_format(giil_day, '%Y-%m-%d') giil_day
mysql> SELECT TIMESTAMP('2003-12-31');
-> '2003-12-31 00:00:00'
mysql> SELECT TIMESTAMP('2003-12-31 12:00:00','12:00:00');
-> '2004-01-01 00:00:00'
mysql> SELECT UNIX_TIMESTAMP();
-> 882226357
mysql> SELECT UNIX_TIMESTAMP('1997-10-04 22:23:00');
-> 875996580
mysql> SELECT FROM_UNIXTIME(1111885200);
+---------------------------+
| FROM_UNIXTIME(1111885200) |
+---------------------------+
| 2005-03-27 03:00:00 |
+---------------------------+
$cur_timestamp = mktime(0,0,0, date("m"), date("d"), date("Y"));
$yes_timestamp = mktime(0,0,0, date("m"), date("d")-1, date("Y"));
$sql = "
select count(*) tot_num,
sum(reg_date >= $yes_timestamp and reg_date < $cur_timestamp) yes_num,
sum(reg_date >= $cur_timestamp) cur_num
from zetyx_member_table
";
$iv_row = @mysql_fetch_array(mysql_query($sql));
?>
##
#
# MS-SQL
#
#####
mssql convert
Conversion to char functions
We outline now the conversions to strings from all the data types (we only specify the MS-SQL Server version of the interpretation of the functions):
Option name in the popup list
Inserted string (where Fld is the selected field)
Float [6-digit]
CONVERT(VARCHAR, Fld, 0)
Float [8-digit Scientific]
CONVERT(VARCHAR, Fld, 1)
Float [16-digit Scientific]
CONVERT(VARCHAR, Fld, 2)
Other Numeric
CONVERT(VARCHAR, Fld)
Currency [9999.99]
CONVERT(VARCHAR, Fld, 0)
Currency [9,999.99]
CONVERT(VARCHAR, Fld, 1)
Currency [9999.9999]
CONVERT(VARCHAR, Fld, 2)
Date-Time [mm/dd/yy]
CONVERT(VARCHAR, Fld, 1)
Date-Time [mm/dd/yyyy]
CONVERT(VARCHAR, Fld, 101 )
Date-Time [yy.mm.dd]
CONVERT(VARCHAR, Fld, 2)
Date-Time [yyyy.mm.dd]
CONVERT(VARCHAR, Fld, 102)
Date-Time [dd/mm/yy]
CONVERT(VARCHAR, Fld, 3)
Date-Time [dd/mm/yyyy]
CONVERT(VARCHAR, Fld, 103 )
Date-Time [dd.mm.yy]
CONVERT(VARCHAR, Fld, 4)
Date-Time [dd.mm.yyyy]
CONVERT(VARCHAR, Fld, 104 )
Date-Time [dd-mm-yy]
CONVERT(VARCHAR, Fld, 5)
Date-Time [dd-mm-yyyy]
CONVERT(VARCHAR, Fld, 105)
Date-Time [dd mon yy]
CONVERT(VARCHAR, Fld, 6)
Date-Time [dd mon yyyy]
CONVERT(VARCHAR, Fld, 106)
Date-Time [Mon dd, yy]
CONVERT(VARCHAR, Fld, 7)
Date-Time [Mon dd, yyyy]
CONVERT(VARCHAR, Fld, 107)
Date-Time [mm-dd-yy]
CONVERT(VARCHAR, Fld, 10)
Date-Time [mm-dd-yyyy]
CONVERT(VARCHAR, Fld, 110 )
Date-Time [yy/mm/dd]
CONVERT(VARCHAR, Fld, 11)
Date-Time [yyyy/mm/dd]
CONVERT(VARCHAR, Fld, 111 )
Date-Time [yymmdd]
CONVERT(VARCHAR, Fld, 12)
Date-Time [yyyymmdd]
CONVERT(VARCHAR, Fld, 112)
Date-Time [mon dd yyyy hh:miAM]
CONVERT(VARCHAR, Fld, 0)
Date-Time [mon dd yyyy hh:mi:ss:mmmAM]
CONVERT(VARCHAR, Fld, 9)
Date-Time [dd mon yyyy hh:mm:ss:mmm]
CONVERT(VARCHAR, Fld, 13)
Date-Time [hh:mm:ss]
CONVERT(VARCHAR, Fld, 8)
Date-Time [hh:mi:ss:mmm]
CONVERT(VARCHAR, Fld, 14)
Date-Time [yyyy-mm-dd hh:mi:ss]
CONVERT(VARCHAR, Fld, 20)
Date-Time [yyyy-mm-dd hh:mi:ss.mmm]
CONVERT(VARCHAR, Fld, 21)
Date-Time [yyyy-mm-ddThh:mm:ss:mmm]
CONVERT(VARCHAR, Fld, 126 )
# 소트 : 주 + 히트수
sSQL = " SELECT TOP 6 ID "
sSQL = sSQL & " , CODE "
sSQL = sSQL & " , TITLE "
sSQL = sSQL & " , HIT "
sSQL = sSQL & " , ASSIST "
sSQL = sSQL & " , IPADDRESS "
sSQL = sSQL & " , create_date "
sSQL = sSQL & " , datepart(yy, create_date)*100+datepart(wk, create_date) as sorttmp "
sSQL = sSQL & " FROM TB_GBOARD_006 "
sSQL = sSQL & " WHERE GUBUN = 'A' "
sSQL = sSQL & " ORDER BY sorttmp desc, HIT DESC "
sSQL = " SELECT TOP 6 ID "
sSQL = sSQL & " , CODE "
sSQL = sSQL & " , TITLE "
sSQL = sSQL & " , HIT "
sSQL = sSQL & " , ASSIST "
sSQL = sSQL & " , IPADDRESS "
sSQL = sSQL & " , create_date "
sSQL = sSQL & " , convert(char, datepart(yy, create_date))+ "
sSQL = sSQL & " convert(char,datepart(wk, create_date)) as sorttmp "
sSQL = sSQL & " FROM TB_GBOARD_018 "
sSQL = sSQL & " WHERE GUBUN = 'A' "
sSQL = sSQL & " ORDER BY sorttmp desc, HIT DESC "
|
