คำสั่ง SQL ในการแปลง string เป็น datetime

wave
มานพ กองอุ่น 25 ก.ย. 2018 16:35:17 71,354

-- SQL Server string to date / datetime conversion - datetime string format sql server 

-- MSSQL string to datetime conversion - convert char to date - convert varchar to date 

-- Subtract 100 from style number (format) for yy instead yyyy (or ccyy with century

SELECT convert(datetime, 'Oct 23 2012 11:01AM', 100) -- mon dd yyyy hh:mmAM (or PM) 

SELECT convert(datetime, 'Oct 23 2012 11:01AM') -- 2012-10-23 11:01:00.000

 

-- Without century (yy) string date conversion - convert string to datetime function 

SELECT convert(datetime, 'Oct 23 12 11:01AM', 0) -- mon dd yy hh:mmAM (or PM) 

SELECT convert(datetime, 'Oct 23 12 11:01AM') -- 2012-10-23 11:01:00.000

 

-- Convert string to datetime sql - convert string to date sql - sql dates format

-- T-SQL convert string to datetime - SQL Server convert string to date 

SELECT convert(datetime, '10/23/2016', 101) -- mm/dd/yyyy 

SELECT convert(datetime, '2016.10.23', 102) -- yyyy.mm.dd ANSI date with century 

SELECT convert(datetime, '23/10/2016', 103) -- dd/mm/yyyy 

SELECT convert(datetime, '23.10.2016', 104) -- dd.mm.yyyy 

SELECT convert(datetime, '23-10-2016', 105) -- dd-mm-yyyy 

-- mon types are nondeterministic conversions, dependent on language setting 

SELECT convert(datetime, '23 OCT 2016', 106) -- dd mon yyyy 

SELECT convert(datetime, 'Oct 23, 2016', 107) -- mon dd, yyyy 

-- 2016-10-23 00:00:00.000

SELECT convert(datetime, '20:10:44', 108) -- hh:mm:ss 

-- 1900-01-01 20:10:44.000

 

-- mon dd yyyy hh:mm:ss:mmmAM (or PM) - sql time format - SQL Server datetime format 

SELECT convert(datetime, 'Oct 23 2016 11:02:44:013AM', 109)

-- 2016-10-23 11:02:44.013

SELECT convert(datetime, '10-23-2016', 110) -- mm-dd-yyyy 

SELECT convert(datetime, '2016/10/23', 111) -- yyyy/mm/dd 

-- YYYYMMDD ISO date format works at any language setting - international standard 

SELECT convert(datetime, '20161023')

SELECT convert(datetime, '20161023', 112) -- ISO yyyymmdd 

-- 2016-10-23 00:00:00.000

SELECT convert(datetime, '23 Oct 2016 11:02:07:577', 113) -- dd mon yyyy hh:mm:ss:mmm 

-- 2016-10-23 11:02:07.577

SELECT convert(datetime, '20:10:25:300', 114) -- hh:mm:ss:mmm(24h) 

-- 1900-01-01 20:10:25.300

SELECT convert(datetime, '2016-10-23 20:44:11', 120) -- yyyy-mm-dd hh:mm:ss(24h) 

-- 2016-10-23 20:44:11.000

SELECT convert(datetime, '2016-10-23 20:44:11.500', 121) -- yyyy-mm-dd hh:mm:ss.mmm 

-- 2016-10-23 20:44:11.500

 

-- Style 126 is ISO 8601 format: international standard - works with any language setting 

SELECT convert(datetime, '2008-10-23T18:52:47.513', 126) -- yyyy-mm-ddThh:mm:ss(.mmm) 

-- 2008-10-23 18:52:47.513

SELECT convert(datetime, N'23 شوال 1429  6:52:47:513PM', 130) -- Islamic/Hijri date 

SELECT convert(datetime, '23/10/1429  6:52:47:513PM',    131) -- Islamic/Hijri date 

 

-- Convert DDMMYYYY format to datetime - sql server to date / datetime

SELECT convert(datetime, STUFF(STUFF('31012016',3,0,'-'),6,0,'-'), 105)

-- 2016-01-31 00:00:00.000

-- SQL Server T-SQL string to datetime conversion without century - some exceptions

-- nondeterministic means language setting dependent such as Mar/Mär/mars/márc

SELECT convert(datetime, 'Oct 23 16 11:02:44AM') -- Default 

SELECT convert(datetime, '10/23/16', 1) -- mm/dd/yy U.S. 

SELECT convert(datetime, '16.10.23', 2) -- yy.mm.dd ANSI

SELECT convert(datetime, '23/10/16', 3) -- dd/mm/yy UK/FR 

SELECT convert(datetime, '23.10.16', 4) -- dd.mm.yy German 

SELECT convert(datetime, '23-10-16', 5) -- dd-mm-yy Italian 

SELECT convert(datetime, '23 OCT 16', 6) -- dd mon yy non-det. 

SELECT convert(datetime, 'Oct 23, 16', 7) -- mon dd, yy non-det. 

SELECT convert(datetime, '20:10:44', 8) -- hh:mm:ss 

SELECT convert(datetime, 'Oct 23 16 11:02:44:013AM', 9) -- Default with msec 

SELECT convert(datetime, '10-23-16', 10) -- mm-dd-yy U.S. 

SELECT convert(datetime, '16/10/23', 11) -- yy/mm/dd Japan 

SELECT convert(datetime, '161023', 12) -- yymmdd ISO 

SELECT convert(datetime, '23 Oct 16 11:02:07:577', 13) -- dd mon yy hh:mm:ss:mmm EU dflt 

SELECT convert(datetime, '20:10:25:300', 14) -- hh:mm:ss:mmm(24h) 

SELECT convert(datetime, '2016-10-23 20:44:11',20) -- yyyy-mm-dd hh:mm:ss(24h) ODBC can. 

SELECT convert(datetime, '2016-10-23 20:44:11.500', 21)-- yyyy-mm-dd hh:mm:ss.mmm ODBC 

------------

-- SQL Datetime Data Type: Combine date & time string into datetime - sql hh mm ss 

-- String to datetime - mssql datetime - sql convert date - sql concatenate string 

DECLARE @DateTimeValue varchar(32), @DateValue char(8), @TimeValue char(6)

 

SELECT @DateValue = '20120718',

       @TimeValue = '211920'

SELECT @DateTimeValue =

convert(varchar, convert(datetime, @DateValue), 111)

+ ' ' + substring(@TimeValue, 1, 2)

+ ':' + substring(@TimeValue, 3, 2)

+ ':' + substring(@TimeValue, 5, 2)

SELECT

DateInput = @DateValue, TimeInput = @TimeValue,

DateTimeOutput = @DateTimeValue;

/*

DateInput   TimeInput   DateTimeOutput

20120718    211920      2012/07/18 21:19:20 */

/* DATETIME 8 bytes internal storage structure

   o 1st 4 bytes: number of days after the base date 1900-01-01

 

   o 2nd 4 bytes: number of clock-ticks (3.33 milliseconds) since midnight

DATETIME2 8 bytes (precision > 4) internal storage structure

   o 1st byte: precision like 7 

   o middle 4 bytes: number of time units (100ns smallest) since midnight

   o last 3 bytes: number of days after the base date 0001-01-01

DATE 3 bytes internal storage structure
   o 3 bytes integer: number of days after the first date 0001-01-01
   o Note: hex byte order reversed

 

SMALLDATETIME 4 bytes internal storage structure
   o 1st 2 bytes: number of days after the base date 1900-01-01

   o 2nd 2 bytes: number of minutes since midnight   */        

SELECT CONVERT(binary(8), getdate()) -- 0x00009E4D 00C01272

SELECT CONVERT(binary(4), convert(smalldatetime,getdate())) -- 0x9E4D 02BC

-- This is how a datetime looks in 8 bytes

DECLARE @dtHex binary(8)= 0x00009966002d3344;

DECLARE @dt datetime = @dtHex

SELECT @dt   -- 2007-07-09 02:44:34.147

------------ */

------------

-- SQL Server 2012 New Date & Time Related Functions

------------

SELECT DATEFROMPARTS ( 2016, 10, 23 ) AS RealDate; -- 2016-10-23

 

SELECT DATETIMEFROMPARTS ( 2016, 10, 23, 10, 10, 10, 500 ) AS RealDateTime; -- 2016-10-23 10:10:10.500

 

SELECT EOMONTH('20140201');       -- 2014-02-28

SELECT EOMONTH('20160201');       -- 2016-02-29

SELECT EOMONTH('20160201',1);     -- 2016-03-31

 

SELECT FORMAT ( getdate(), 'yyyy/MM/dd hh:mm:ss tt', 'en-US' );   -- 2016/07/30 03:39:48 AM

SELECT FORMAT ( getdate(), 'd', 'en-US' );                        -- 7/30/2016

 

SELECT PARSE('SAT, 13 December 2014' AS datetime USING 'en-US') AS [Date&Time];  

-- 2014-12-13 00:00:00.000

 

SELECT TRY_PARSE('SAT, 13 December 2014' AS datetime USING 'en-US') AS [Date&Time];  

-- 2014-12-13 00:00:00.000

 

SELECT TRY_CONVERT(datetime, '13 December 2014' ) AS [Date&Time];  -- 2014-12-13 00:00:00.000

SELECT CONVERT(datetime2, sysdatetime()); AS [DateTime2];  -- 2016-02-12 13:09:24.0642891

------------

 

-- SQL convert seconds to HH:MM:SS - sql times format - sql hh mm 

DECLARE  @Seconds INT

SET @Seconds = 20000 

SELECT HH = @Seconds / 3600, MM = (@Seconds%3600) / 60, SS = (@Seconds%60)

/* HH    MM    SS

  5     33    20   */

------------

-- SQL Server Date Only from DATETIME column - get date only 

-- T-SQL just date - truncate time from datetime - remove time part

------------

DECLARE @Now datetime = CURRENT_TIMESTAMP -- getdate()

SELECT  DateAndTime       = @Now      -- Date portion and Time portion

       ,DateString        = REPLACE(LEFT(CONVERT (varchar, @Now, 112),10),' ','-')

       ,[Date]            = CONVERT(DATE, @Now)  -- SQL Server 2008 and on - date part

       ,Midnight1         = dateadd(day, datediff(day,0, @Now), 0)

       ,Midnight2         = CONVERT(DATETIME,CONVERT(int, @Now))

       ,Midnight3         = CONVERT(DATETIME,CONVERT(BIGINT,@Now) &                                                           (POWER(Convert(bigint,2),32)-1))

/* DateAndTime    DateString  Date  Midnight1   Midnight2   Midnight3

2010-11-02 08:00:33.657 20101102    2010-11-02  2010-11-02 00:00:00.000 2010-11-02 00:00:00.000      2010-11-02 00:00:00.000 */

------------

-- SQL Server 2008 convert datetime to date - sql yyyy mm dd 

SELECT      TOP (3)  OrderDate = CONVERT(date, OrderDate),

            Today = CONVERT(date, getdate())

FROM AdventureWorks2008.Sales.SalesOrderHeader

ORDER BY newid();

/*          OrderDate   Today

            2004-02-15  2012-06-18 .....*/

------------

-- SQL date yyyy mm dd - sqlserver yyyy mm dd - date format yyyymmdd

SELECT CONVERT(VARCHAR(10), GETDATE(), 111) AS [YYYY/MM/DD] 

/*  YYYY/MM/DD

    2015/07/11    */

SELECT CONVERT(VARCHAR(10), GETDATE(), 112) AS [YYYYMMDD] 

/*  YYYYMMDD

    20150711     */

SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 111),'/',' ') AS [YYYY MM DD] 

/* YYYY MM DD

   2015 07 11    */
-- Converting to special (non-standard) date fomats: DD-MMM-YY
SELECT UPPER(REPLACE(CONVERT(VARCHAR,GETDATE(),6),' ','-'))
-- 07-MAR-14
------------

-- SQL convert date string to datetime - time set to 00:00:00.000 or 12:00AM

PRINT CONVERT(datetime,'07-10-2012',110)        -- Jul 10 2012 12:00AM

PRINT CONVERT(datetime,'2012/07/10',111)        -- Jul 10 2012 12:00AM 

PRINT CONVERT(datetime,'20120710',  112)        -- Jul 10 2012 12:00AM           

------------

-- UNIX to SQL Server datetime conversion       

declare @UNIX bigint  = 1477216861;

select dateadd(ss,@UNIX,'19700101'); -- 2016-10-23 10:01:01.000
------------

-- String to date conversion - sql date yyyy mm dd - sql date formatting 

-- SQL Server cast string to date - sql convert date to datetime

SELECT [Date] = CAST (@DateValue AS datetime)

-- 2012-07-18 00:00:00.000

 

-- SQL convert string date to different style - sql date string formatting

SELECT CONVERT(varchar, CONVERT(datetime, '20140508'), 100)

-- May  8 2014 12:00AM

-- SQL Server convert date to integer

DECLARE @Date datetime; SET @Date = getdate();

SELECT DateAsInteger = CAST (CONVERT(varchar,@Date,112) as INT);

-- Result: 20161225

 

-- SQL Server convert integer to datetime

DECLARE @iDate int

SET @iDate = 20151225

SELECT IntegerToDatetime = CAST(convert(varchar,@iDate) as datetime)

-- 2015-12-25 00:00:00.000

 

-- Alternates: date-only datetime values

-- SQL Server floor date - sql convert datetime 

SELECT [DATE-ONLY]=CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, GETDATE())))

SELECT [DATE-ONLY]=CONVERT(DATETIME, FLOOR(CONVERT(MONEY, GETDATE())))

-- SQL Server cast string to datetime

-- SQL Server datetime to string convert

SELECT [DATE-ONLY]=CAST(CONVERT(varchar, GETDATE(), 101) AS DATETIME)

-- SQL Server dateadd function - T-SQL datediff function

-- SQL strip time from date - MSSQL strip time from datetime

SELECT getdate() ,dateadd(dd, datediff(dd, 0, getdate()), 0)

-- Results: 2016-01-23 05:35:52.793 2016-01-23 00:00:00.000

-- String date  - 10 bytes of storage

SELECT [STRING DATE]=CONVERT(varchar,  GETDATE(), 110)

SELECT [STRING DATE]=CONVERT(varchar,  CURRENT_TIMESTAMP, 110)

-- Same results: 01-02-2012

 

-- SQL Server cast datetime as string - sql datetime formatting 

SELECT stringDateTime=CAST (getdate() as varchar) -- Dec 29 2012  3:47AM

 

 

----------

-- SQL date range BETWEEN operator

----------

-- SQL date range select - date range search - T-SQL date range query 

-- Count Sales Orders for 2003 OCT-NOV 

DECLARE  @StartDate DATETIME,  @EndDate DATETIME

SET @StartDate = convert(DATETIME,'10/01/2003',101)

SET @EndDate   = convert(DATETIME,'11/30/2003',101)

 

SELECT @StartDate, @EndDate 

-- 2003-10-01 00:00:00.000  2003-11-30 00:00:00.000 

SELECT dateadd(DAY,1,@EndDate),

       dateadd(ms,-3,dateadd(DAY,1,@EndDate))

-- 2003-12-01 00:00:00.000  2003-11-30 23:59:59.997 

 

-- MSSQL date range select using >= and <

SELECT [Sales Orders for 2003 OCT-NOV] = COUNT(* )

FROM   Sales.SalesOrderHeader 

WHERE  OrderDate >= @StartDate AND OrderDate < dateadd(DAY,1,@EndDate)

/* Sales Orders for 2003 OCT-NOV

   3668 */

 

-- Equivalent date range query using BETWEEN comparison 

-- It requires a bit of trick programming 

SELECT [Sales Orders for 2003 OCT-NOV] = COUNT(* )

FROM   Sales.SalesOrderHeader 

WHERE  OrderDate BETWEEN @StartDate AND dateadd(ms,-3,dateadd(DAY,1,@EndDate))

-- 3668

 

USE AdventureWorks


ความคิดเห็น

หากบทเรียนรู้มีความผิดพลาดประการใด หรือมีข้อเสนอแนะกรุณาแจ้ง contact@programmerthailand.com