หมายเหตุ
- ในอนาคตจะเพิ่มอีก version คือ Javascript สำหรับการทำงานร่วมกันกับ AJAX หรือ PHP หรือ JAVA
- สำหรับ vb.net ก้อเอา code ไปแปลงเอา นะคะ >>http:// http://converter.telerik.com/
- version store proc มี function เพิ่มเติมต้องสร้างให้ครบจึงจะใช้งานได้
- ทุก version ทำงานกับปีฝรั่งค่ะ >> AD >> ANO DOMINI
ตัวอย่างการใช้งาน
prototype version (c#) :
DateTime myDate = new DateTime(2532 - 543, 4, 7);
DateTime ToDate = DateTime.Now;
DateDifference dDiff = new DateDifference(myDate, ToDate);
SomeLabel.Text = dDiff .ToString() ;
extension method version :
DateTime myDate = new DateTime(2532 - 543, 4, 7);
DateTime ToDate = DateTime.Now;
SomeLabel.Text = myDate.CalcAge(ToDate).ToString()
MS SQL STOREPROC:
SELECT dbo.CALC_AGE([Date_Field] ,GETDATE() ,0) As [PreciseAge]
FROM [TableName]
WHERE ....
MY SQL STOREPROC:
SELECT CALC_AGE(`Date_Field` ,CURDATE() ,0) As `PreciseAge`
FROM `TableName`
WHERE ....
เพื่อความสะดวกจะ post 1 reply per version ค่ะ
ถ้าพบข้อผิดพลาดประการใด กรุณาแจ้งมาทาง PM ขอบพระคุณมากค่ะ
Tag : .NET, MySQL, Ms SQL Server 2008, C#, VS 2003 (.NET 1.1), VS 2010 (.NET 4.x)
/****** Object: UserDefinedFunction [dbo].[RPAD] Script Date: 09/29/2010 05:54:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: PROUD
-- Create date: <Create Date, ,>
-- Description: <Description, ,>
-- =============================================
CREATE FUNCTION [dbo].[RPAD] (@VAR_STR NVARCHAR(MAX) ,@VAR_LENGTH INT ,@VAR_FILL_CHAR NVARCHAR(MAX))
RETURNS NVARCHAR(MAX)
AS
BEGIN
DECLARE
@TEMP INT ,
@COUNTER INT ,
@RETSTR NVARCHAR(MAX) ;
SET @TEMP = @VAR_LENGTH - LEN(@VAR_STR);
SET @RETSTR = @VAR_STR ;
IF (@TEMP>0)
BEGIN
SET @COUNTER = 0;
WHILE (@COUNTER < @TEMP)
BEGIN
SET @RETSTR = @RETSTR + @VAR_FILL_CHAR ;
SET @COUNTER = @COUNTER +1 ;
END
END
RETURN @RETSTR
END
GO
/****** Object: UserDefinedFunction [dbo].[LPAD] Script Date: 09/29/2010 05:55:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: PROUD
-- Create date: <Create Date, ,>
-- Description: <Description, ,>
-- =============================================
CREATE FUNCTION [dbo].[LPAD] (@VAR_STR NVARCHAR(MAX) ,@VAR_LENGTH INT ,@VAR_FILL_CHAR NVARCHAR(MAX))
RETURNS NVARCHAR(MAX)
AS
BEGIN
DECLARE
@TEMP INT ,
@COUNTER INT ,
@RETSTR NVARCHAR(MAX) ;
SET @TEMP = @VAR_LENGTH - LEN(@VAR_STR);
SET @RETSTR = @VAR_STR ;
IF (@TEMP>0)
BEGIN
SET @COUNTER = 0;
WHILE (@COUNTER < @TEMP)
BEGIN
SET @RETSTR = @VAR_FILL_CHAR + @RETSTR ;
SET @COUNTER = @COUNTER +1 ;
END
END
RETURN @RETSTR
END
GO
/****** Object: UserDefinedFunction [dbo].[DATE_SERIAL] Script Date: 09/29/2010 05:55:31 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- ======================================================
-- Author: <PROUD ,PRANAKHON RAJABHUTNUNIVERSITY>
-- Create date: <12 DEC 2009>
-- Description: <CREATE DATETIME WITH EXACTLY FORMAT>
-- ======================================================
CREATE FUNCTION [dbo].[DATE_SERIAL](@VAR_YEARPART INT ,@VAR_MONTHPART INT ,@VAR_DAYPART INT)
RETURNS DATETIME
AS
BEGIN
DECLARE
@TEMP_STR VARCHAR(20) ,
@RET_DATE DATETIME;
-- BEGIN CALCULATION
SET @TEMP_STR = CONVERT(VARCHAR ,@VAR_DAYPART) + '-'
+ CONVERT(VARCHAR ,@VAR_MONTHPART) + '-'
+ CONVERT(VARCHAR ,@VAR_YEARPART) ;
SET @RET_DATE = CONVERT(DATETIME ,@TEMP_STR ,105) ;
RETURN @RET_DATE ;
END
/****** Object: UserDefinedFunction [dbo].[CALC_AGE] Script Date: 09/29/2010 05:56:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- ======================================================
-- Author: <PROUD ,PRANAKHON RAJABHUTNUNIVERSITY>
-- Create date: <25 SEPT2010>
-- Description: <CALC PRECISE AGE>
-- ======================================================
CREATE FUNCTION [dbo].[CALC_AGE]
(
-- Add the parameters for the function here
@VAR_FROM_DATE DATETIME ,@VAR_TO_DATE DATETIME ,@OUTPUT_PATTERN INT
)
RETURNS VARCHAR(100)
AS
BEGIN
-- Declare the return variable here
DECLARE @TEMP INT ;
DECLARE @FROM_DATE DATETIME ;
DECLARE @TO_DATE DATETIME ;
DECLARE @TEMP_DATE DATETIME ;
DECLARE @CARRY_FLAG INT ;
DECLARE @MY_YEAR INT ;
DECLARE @MY_MONTH INT ;
DECLARE @MY_DAY INT ;
DECLARE @RET_STR VARCHAR(100) ;
SET @CARRY_FLAG = 0 ;
-- REORDER
IF (DATEDIFF(DAY ,@VAR_FROM_DATE ,@VAR_TO_DATE) >= 0 )
BEGIN
SET @FROM_DATE = @VAR_FROM_DATE;
SET @TO_DATE = @VAR_TO_DATE ;
END
ELSE
BEGIN
SET @FROM_DATE = @VAR_TO_DATE;
SET @TO_DATE = @VAR_FROM_DATE ;
END ;
-- DAY CALCULATION
IF (DAY(@FROM_DATE) > DAY(@TO_DATE))
BEGIN
SET @TEMP_DATE = dbo.DATE_SERIAL(YEAR(@FROM_DATE) ,MONTH(@FROM_DATE) ,1) ;
SET @TEMP_DATE = DATEADD(MONTH ,1 ,@TEMP_DATE) ;
SET @TEMP_DATE = DATEADD(DAY ,-1 ,@TEMP_DATE) ;
SET @CARRY_FLAG = DAY(@TEMP_DATE) ;
SET @MY_DAY = (DAY(@TO_DATE) + @CARRY_FLAG) - DAY(@FROM_DATE) ;
SET @CARRY_FLAG = 1 ;
END
ELSE
SET @MY_DAY = DAY(@TO_DATE) - DAY(@FROM_DATE) ;
-- MONTH
IF ((MONTH(@FROM_DATE) + @CARRY_FLAG) > MONTH(@TO_DATE) )
BEGIN
SET @MY_MONTH = (MONTH(@TO_DATE) + 12) - (MONTH(@FROM_DATE) + @CARRY_FLAG) ;
SET @CARRY_FLAG = 1 ;
END
ELSE
BEGIN
SET @MY_MONTH = MONTH(@TO_DATE) - (MONTH(@FROM_DATE) + @CARRY_FLAG) ;
SET @CARRY_FLAG = 0 ;
END ;
--YEAR
SET @MY_YEAR = YEAR(@TO_DATE) - (YEAR(@FROM_DATE) + @CARRY_FLAG) ;
IF (@OUTPUT_PATTERN = 99)
SET @RET_STR = dbo.LPAD(@MY_YEAR ,4 ,'0') + '.' + dbo.LPAD(@MY_MONTH ,2 ,'0')
+ '.' + dbo.LPAD(@MY_DAY ,2 ,'0') ;
ELSE
BEGIN
SET @RET_STR = '' ;
IF (@MY_YEAR > 0 )
SET @RET_STR = CONVERT(VARCHAR(100) ,@MY_YEAR ) + ' Year(s) ' ;
IF (@MY_MONTH>0)
SET @RET_STR = @RET_STR + CONVERT(VARCHAR(100) ,@MY_MONTH ) + ' Month(s) ' ;
IF (@MY_DAY>0)
SET @RET_STR = @RET_STR + CONVERT(VARCHAR(100) ,@MY_DAY ) + ' Day(s) ' ;
END ;
-- Return the result of the function
RETURN @RET_STR
END
DELIMITER $$
DROP FUNCTION IF EXISTS `DateSerial` $$
CREATE DEFINER=`root`@`localhost` FUNCTION `DateSerial`(VARYEAR INT ,VARMONTH INT ,VARDAY INT) RETURNS date
BEGIN
DECLARE TEMPSTR TEXT ;
SET TEMPSTR = CONCAT(VARDAY ,' ' ,VARMONTH ,' ' ,VARYEAR) ;
RETURN STR_TO_DATE( TEMPSTR ,'%d %m %Y') ;
END $$
DELIMITER ;
Code (C#)
DELIMITER $$
DROP FUNCTION IF EXISTS `CALC_AGE` $$
CREATE DEFINER=`root`@`localhost` FUNCTION `CALC_AGE`(
_VAR_FROM_DATE DATE ,_VAR_TO_DATE DATE ,_VAR_OUPUT_PATTERN INT
) RETURNS varchar(100) CHARSET tis620
BEGIN
DECLARE _RET_STR VARCHAR(100) DEFAULT '' ;
DECLARE _FROM_DATE DATE ;
DECLARE _TO_DATE DATE ;
DECLARE _TEMP_DATE DATE ;
DECLARE _CARRY_FLAG INT DEFAULT 0 ;
DECLARE _MY_YEAR INT DEFAULT 0 ;
DECLARE _MY_MONTH INT DEFAULT 0 ;
DECLARE _MY_DAY INT DEFAULT 0 ;
-- IN MYSQL GREATER THAN DATE COME FIRST IN DATEDIFF FUNC.
IF (DATEDIFF(_VAR_TO_DATE ,_VAR_FROM_DATE)>=0) THEN
BEGIN
SET _TO_DATE = _VAR_TO_DATE ;
SET _FROM_DATE = _VAR_FROM_DATE ;
END ;
ELSE
BEGIN
SET _TO_DATE = _VAR_FROM_DATE ;
SET _FROM_DATE = _VAR_TO_DATE ;
END;
END IF;
-- DAY CALC
IF (DAY(_FROM_DATE) > DAY(_TO_DATE)) THEN
BEGIN
-- FIND LAST DAY OF THIS MONTH
SET _TEMP_DATE = DATESERIAL(YEAR(_FROM_DATE) ,MONTH(_fROM_DATE) ,1) ;
SET _TEMP_DATE = DATE_ADD(_TEMP_DATE ,INTERVAL 1 MONTH) ;
SET _TEMP_DATE = DATE_ADD(_TEMP_DATE ,INTERVAL -1 DAY) ;
SET _CARRY_FLAG = DAY(_TEMP_DATE) ;
SET _MY_DAY = (DAY(_TO_DATE) + _CARRY_FLAG) - DAY(_FROM_DATE) ;
SET _CARRY_FLAG = 1 ;
END ;
ELSE
SET _MY_DAY = DAY(_TO_DATE) - DAY(_FROM_DATE) ;
END IF ;
-- MONTH CALC
IF ((MONTH(_FROM_DATE) + _CARRY_FLAG) > MONTH(_TO_DATE)) THEN
BEGIN
SET _MY_MONTH = (MONTH(_TO_DATE) + 12) - (MONTH(_FROM_DATE) + _CARRY_FLAG) ;
SET _CARRY_FLAG = 1 ;
END ;
ELSE
BEGIN
SET _MY_MONTH = MONTH(_TO_DATE) - (MONTH(_FROM_DATE) + _CARRY_FLAG) ;
SET _CARRY_FLAG = 0 ;
END ;
END IF ;
-- YEAR CALC
SET _MY_YEAR = YEAR(_TO_DATE) - (YEAR(_FROM_DATE) + _CARRY_FLAG) ;
CASE _VAR_OUPUT_PATTERN
WHEN 99 THEN
SET _RET_STR = CONCAT(LPAD(_MY_YEAR ,4 ,'0') ,'.'
,LPAD(_MY_MONTH ,2 ,'0') ,'.'
,LPAD(_MY_DAY ,2 ,'0')) ;
ELSE
BEGIN
IF(_MY_YEAR > 0 ) THEN
SET _RET_STR = CONCAT(FORMAT(_MY_YEAR ,0) ,' Year(s) ') ;
END IF ;
IF(_MY_MONTH > 0 ) THEN
BEGIN
IF (LENGTH(_RET_STR)>0) THEN
SET _RET_STR = CONCAT(_RET_STR ,', ') ;
END IF ;
SET _RET_STR = CONCAT(_RET_STR ,FORMAT(_MY_MONTH ,0) ,' Month(s) ') ;
END ;
END IF ;
IF(_MY_DAY > 0 ) THEN
BEGIN
IF (LENGTH(_RET_STR)>0) THEN
SET _RET_STR = CONCAT(_RET_STR ,', ') ;
END IF ;
SET _RET_STR = CONCAT(_RET_STR ,FORMAT(_MY_DAY ,0) ,' Day(s) ') ;
END ;
END IF ;
END ;
END CASE ;
RETURN _RET_STR;
END $$
DELIMITER ;
Load balance : Server 02
