-- Microsoft SQL Server string to date conversion - datetime string format sql server
-- MSSQL string to datetime conversion - convert char to date sql server
-- 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
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
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
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
SELECT convert(datetime, '20161023', 112) -- 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
SELECT convert(datetime, '2008-10-23T18:52:47.513', 126) -- yyyy-mm-ddThh:mm:ss.mmm
-- 2008-10-23 18:52:47.513
-- Convert DDMMYYYY format to datetime
SELECT convert(datetime, STUFF(STUFF('31012016',3,0,'-'),6,0,'-'), 105)
-- 2016-01-31 00:00:00.000
-- SQL string to datetime conversion without century - some exceptions
SELECT convert(datetime, '10/23/16', 1) -- mm/dd/yy
SELECT convert(datetime, '16.10.23', 2) -- yy.mm.dd
SELECT convert(datetime, '23/10/16', 3) -- dd/mm/yy
SELECT convert(datetime, '23.10.16', 4) -- dd.mm.yy
SELECT convert(datetime, '23-10-16', 5) -- dd-mm-yy
SELECT convert(datetime, '23 OCT 16', 6) -- dd mon yy
SELECT convert(datetime, 'Oct 23, 16', 7) -- mon dd, yy
SELECT convert(datetime, '20:10:44', 8) -- hh:mm:ss
SELECT convert(datetime, 'Oct 23 16 11:02:44:013AM', 9)
SELECT convert(datetime, '10-23-16', 10) -- mm-dd-yy
SELECT convert(datetime, '16/10/23', 11) -- yy/mm/dd
SELECT convert(datetime, '161023', 12) -- yymmdd
SELECT convert(datetime, '23 Oct 16 11:02:07:577', 13) -- dd mon yy hh:mm:ss:mmm
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)
SELECT convert(datetime, '2016-10-23 20:44:11.500', 21) -- yyyy-mm-dd hh:mm:ss.mmm
------------
-- 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
*/
-- 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)
GO
/*
HH MM SS
5 33 20
*/
------------
-- 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
2003-07-09 2012-06-18
2003-09-26 2012-06-18
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 */
------------
-- 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
------------
-- 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)
GO
-- 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)
GO
-- 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)
--Result: Dec 29 2012 3:47AM
----------
-- SQL date range between
----------
-- SQL date range select - date range search
-- T-SQL date range query - sql date ranges
-- 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
GO
USE AdventureWorks;
-- SQL between string dates
SELECT POs=COUNT(*) FROM Purchasing.PurchaseOrderHeader
WHERE OrderDate BETWEEN '20040201' AND '20040210'
-- Result: 108
-- BETWEEN is equivalent to >=...AND....<=
SELECT POs=COUNT(*) FROM Purchasing.PurchaseOrderHeader
WHERE OrderDate
BETWEEN '2004-02-01 00:00:00.000' AND '2004-02-10 00:00:00.000'
/*
Orders with OrderDates
'2004-02-10 00:00:01.000' - 1 second after midnight (12:00AM)
'2004-02-10 00:01:00.000' - 1 minute after midnight
'2004-02-10 01:00:00.000' - 1 hour after midnight
are not included in the two queries above.
*/
-- To include the entire day of 2004-02-10 use:
SELECT POs=COUNT(*) FROM Purchasing.PurchaseOrderHeader
WHERE OrderDate >= '20040201' AND OrderDate < '20040211'
----------
-- Date validation function ISDATE - returns 1 or 0
-- SQL datetime functions
------------
DECLARE @StringDate varchar(32)
SET @StringDate = '2011-03-15 18:50'
IF EXISTS( SELECT * WHERE ISDATE(@StringDate) = 1)
PRINT 'VALID DATE: ' + @StringDate
ELSE
PRINT 'INVALID DATE: ' + @StringDate
GO
-- Result: VALID DATE: 2011-03-15 18:50
DECLARE @StringDate varchar(32)
SET @StringDate = '20112-03-15 18:50'
IF EXISTS( SELECT * WHERE ISDATE(@StringDate) = 1)
PRINT 'VALID DATE: ' + @StringDate
ELSE
PRINT 'INVALID DATE: ' + @StringDate
GO
-- Result: INVALID DATE: 20112-03-15 18:50
-- Selected named date styles
------------
DECLARE @DateTimeValue varchar(32)
-- US-Style
SELECT @DateTimeValue = '10/23/2016'
SELECT StringDate=@DateTimeValue,
[US-Style] = CONVERT(datetime, @DatetimeValue)
SELECT @DateTimeValue = '10/23/2016 23:01:05'
SELECT StringDate = @DateTimeValue,
[US-Style] = CONVERT(datetime, @DatetimeValue)
-- UK-Style, British/French
-- convert string to datetime sql
-- sql convert string to datetime
SELECT @DateTimeValue = '23/10/16 23:01:05'
SELECT StringDate = @DateTimeValue,
[UK-Style] = CONVERT(datetime, @DatetimeValue, 3)
SELECT @DateTimeValue = '23/10/2016 04:01 PM'
SELECT StringDate = @DateTimeValue,
[UK-Style] = CONVERT(datetime, @DatetimeValue, 103)
-- German-Style
SELECT @DateTimeValue = '23.10.16 23:01:05'
SELECT StringDate = @DateTimeValue,
[German-Style] = CONVERT(datetime, @DatetimeValue, 4)
SELECT @DateTimeValue = '23.10.2016 04:01 PM'
SELECT StringDate = @DateTimeValue,
[German-Style] = CONVERT(datetime, @DatetimeValue, 104)
------------
-- Double conversion to US-Style 107 with century: Oct 23, 2016
SET @DateTimeValue='10/23/16'
SELECT StringDate=@DateTimeValue,
[US-Style] = CONVERT(varchar, CONVERT(datetime, @DateTimeValue),107)
-- Using DATEFORMAT - UK-Style
-- SQL dateformat
SET @DateTimeValue='23/10/16'
SET DATEFORMAT dmy
SELECT StringDate=@DateTimeValue,
[Date Time] = CONVERT(datetime, @DatetimeValue)
-- Using DATEFORMAT - US-Style
SET DATEFORMAT mdy
-- DATEPART datetime function example
-- SQL Server datetime functions
SELECT * FROM Northwind.dbo.Orders
WHERE
DATEPART(YEAR, OrderDate) = '1996' AND
DATEPART(MONTH,OrderDate) = '07' AND
DATEPART(DAY, OrderDate) = '10'
-- Alternate syntax for DATEPART example
SELECT * FROM Northwind.dbo.Orders
WHERE
YEAR(OrderDate) = '1996' AND
MONTH(OrderDate) = '07' AND
DAY(OrderDate) = '10'
GO
------------
-- Extract string date from text with PATINDEX pattern matching
-- Apply sql server string to date conversion
------------
USE tempdb;
go
CREATE TABLE InsiderTransaction (
InsiderTransactionID int identity primary key,
TradeDate datetime,
TradeMsg varchar(256),
ModifiedDate datetime default (getdate())
)
go
-- Populate table with dummy data
INSERT InsiderTransaction (TradeMsg) VALUES(
'INSIDER TRAN QABC Hammer, Bruce D. CSO 09-02-08 Buy 2,000 6.10')
INSERT InsiderTransaction (TradeMsg) VALUES(
'INSIDER TRAN QABC Schmidt, Steven CFO 08-25-08 Buy 2,500 6.70')
INSERT InsiderTransaction (TradeMsg) VALUES(
'INSIDER TRAN QABC Hammer, Bruce D. CSO 08-20-08 Buy 3,000 8.59')
INSERT InsiderTransaction (TradeMsg) VALUES(
'INSIDER TRAN QABC Walters, Jeff CTO 08-15-08 Sell 5,648 8.49')
INSERT InsiderTransaction (TradeMsg) VALUES(
'INSIDER TRAN QABC Walters, Jeff CTO 08-15-08 Option Execute 5,648 2.15')
INSERT InsiderTransaction (TradeMsg) VALUES(
'INSIDER TRAN QABC Hammer, Bruce D. CSO 07-31-08 Buy 5,000 8.05')
INSERT InsiderTransaction (TradeMsg) VALUES(
'INSIDER TRAN QABC Lennot, Mark B. Director 08-31-07 Buy 1,500 9.97')
INSERT InsiderTransaction (TradeMsg) VALUES(
'INSIDER TRAN QABC O''Neal, Linda COO 08-01-08 Sell 5,000 6.50')
go
-- Extract dates from stock trade message text
-- Pattern match for MM-DD-YY using the PATINDEX string function
SELECT TradeDate=substring(TradeMsg,
patindex('%[01][0-9]-[0123][0-9]-[0-9][0-9]%', TradeMsg),8)
FROM InsiderTransaction
WHERE patindex('%[01][0-9]-[0123][0-9]-[0-9][0-9]%', TradeMsg) > 0
/* Partial results
TradeDate
09-02-08
08-25-08
08-20-08
*/
-- Update table with extracted date
-- Convert string date to datetime
UPDATE InsiderTransaction
SET TradeDate = convert(datetime, substring(TradeMsg,
patindex('%[01][0-9]-[0123][0-9]-[0-9][0-9]%', TradeMsg),8))
WHERE patindex('%[01][0-9]-[0123][0-9]-[0-9][0-9]%', TradeMsg) > 0
SELECT * FROM InsiderTransaction ORDER BY TradeDate desc
/* Partial results
InsiderTransactionID TradeDate TradeMsg ModifiedDate
1 2008-09-02 00:00:00.000 INSIDER TRAN QABC Hammer, Bruce D. CSO 09-02-08 Buy 2,000 6.10 2008-12-22 20:25:19.263
2 2008-08-25 00:00:00.000 INSIDER TRAN QABC Schmidt, Steven CFO 08-25-08 Buy 2,500 6.70 2008-12-22 20:25:19.263
3 2008-08-20 00:00:00.000 INSIDER TRAN QABC Hammer, Bruce D. CSO 08-20-08 Buy 3,000 8.59 2008-12-22 20:25:19.263
*/
-- Cleanup task
DROP TABLE InsiderTransaction
go
/************
VALID DATE RANGES FOR DATETIME DATA TYPES
SMALLDATETIME (4 bytes) date range:
January 1, 1900 through June 6, 2079
DATETIME (8 bytes) date range:
January 1, 1753 through December 31, 9999
-- The statement below will give a date range error
SELECT CONVERT(smalldatetime, '2110-01-01')
/*
Msg 242, Level 16, State 3, Line 1
The conversion of a varchar data type to a smalldatetime data type
resulted in an out-of-range value.
*/
************/
------------
-- SQL CONVERT DATE/DATETIME script applying table variable
------------
-- SQL Server convert date
-- Datetime column is converted into date only string column
DECLARE @sqlConvertDate TABLE ( DatetimeColumn datetime,
DateColumn char(10));
INSERT @sqlConvertDate(DatetimeColumn) SELECT GETDATE()
UPDATE @sqlConvertDate
SET DateColumn = CONVERT(char(10), DatetimeColumn, 111)
SELECT * FROM @sqlConvertDate
-- SQL Server convert datetime
-- The string date column is converted into datetime column
UPDATE @sqlConvertDate
SET DatetimeColumn = CONVERT(Datetime, DateColumn, 111)
SELECT * FROM @sqlConvertDate
-- Equivalent formulation
-- SQL Server cast datetime
UPDATE @sqlConvertDate
SET DatetimeColumn = CAST(DateColumn AS datetime)
SELECT * FROM @sqlConvertDate
GO
/* First results
DatetimeColumn DateColumn
2012-12-25 15:54:10.363 2012/12/25 */
/* Second results:
DatetimeColumn DateColumn
2012-12-25 00:00:00.000 2012/12/25 */
------------
-- SQL date sequence generation with DATEADD & table variable
-- SQL Server cast datetime to string
-- SQL Server insert default values method
DECLARE @Sequence table (Sequence int identity(1,1))
DECLARE @i int; SET @i = 0
WHILE ( @i < 500)
BEGIN
INSERT @Sequence DEFAULT VALUES
SET @i = @i + 1
END
SELECT DateSequence = CAST(DATEADD(day, Sequence,getdate()) AS varchar)
FROM @Sequence
GO
/* Partial results:
DateSequence
Dec 31 2008 3:02AM
Jan 1 2009 3:02AM
Jan 2 2009 3:02AM
Jan 3 2009 3:02AM
Jan 4 2009 3:02AM
Jan 5 2009 3:02AM
Jan 6 2009 3:02AM
Jan 7 2009 3:02AM
*/
------------
------------
-- SQL Last Week calculations
------------
-- SQL last Friday
-- Implied string to datetime conversions in DATEADD & DATEDIFF
DECLARE @BaseFriday CHAR(8), @LastFriday datetime, @LastMonday datetime
SET @BaseFriday = '19000105'
SELECT @LastFriday = DATEADD(dd,
(DATEDIFF(dd, @BaseFriday, CURRENT_TIMESTAMP) / 7) * 7,
@BaseFriday)
SELECT [Last Friday] = @LastFriday
-- Result: 2008-12-26 00:00:00.000
-- SQL last Monday (last week's Monday)
SELECT @LastMonday=DATEADD(dd,
(DATEDIFF(dd, @BaseFriday, CURRENT_TIMESTAMP) / 7) * 7 - 4,
@BaseFriday)
SELECT [Last Monday]= @LastMonday
-- Result: 2008-12-22 00:00:00.000
-- SQL last week - SUN - SAT
SELECT [Last Week] = CONVERT(varchar,dateadd(day, -1, @LastMonday), 101)+
' - ' +
CONVERT(varchar,dateadd(day, 1, @LastFriday), 101)
-- Result: 12/21/2008 - 12/27/2008
------------
------------
-- SQL Last Date calculations
------------
-- Last day of prior month
-- Last day of previous month
SELECT convert( varchar, dateadd(dd,-1,DATEADD(mm, DATEDIFF(mm,0,getdate() ), 0)),101)
-- 01/31/2019
-- Last day of current month
SELECT convert( varchar, dateadd(dd,-1,DATEADD(mm, DATEDIFF(mm,0,getdate())+1, 0)),101)
-- 02/28/2019
-- Last day of prior quarter
-- Last day of previous quarter
SELECT convert( varchar, dateadd(dd,-1,DATEADD(qq, DATEDIFF(qq,0,getdate() ), 0)),101)
-- 12/31/2018
-- Last day of current quarter
-- Last day of current quarter
SELECT convert( varchar, dateadd(dd,-1,DATEADD(qq, DATEDIFF(qq,0,getdate())+1, 0)),101)
-- 03/31/2019
-- Last day of prior year
-- Last day of previous year
SELECT convert( varchar, dateadd(dd,-1,DATEADD(yy, DATEDIFF(yy,0,getdate() ), 0)),101)
-- 12/31/2018
-- Last day of current year
SELECT convert( varchar, dateadd(dd,-1,DATEADD(yy, DATEDIFF(yy,0,getdate())+1, 0)),101)
-- 12/31/2019
GO
------------
------------
-- SQL Server dateformat and language setting
------------
-- T-SQL set language
-- String to date conversion
SET LANGUAGE us_english
SELECT CAST('2018-03-15' AS datetime)
-- 2018-03-15 00:00:00.000
SET LANGUAGE british
SELECT CAST('2018-03-15' AS datetime)
/* Msg 242, Level 16, State 3, Line 2
The conversion of a varchar data type to a datetime data type resulted in
an out-of-range value.
*/
SELECT CAST('2018-15-03' AS datetime)
-- 2018-03-15 00:00:00.000
SET LANGUAGE us_english
-- SQL dateformat with language dependency
SELECT name, alias, dateformat
FROM sys.syslanguages
WHERE langid in(0,1,2,4,5,6,7,10,11,13,23,31)
GO
/*
name alias dateformat
us_english English mdy
Deutsch German dmy
Français French dmy
Dansk Danish dmy
Español Spanish dmy
Italiano Italian dmy
Nederlands Dutch dmy
Suomi Finnish dmy
Svenska Swedish ymd
magyar Hungarian ymd
British British English dmy
Arabic Arabic dmy
*/
------------
Sql Server 中一个非常强大的日期格式化函数
Select CONVERT(varchar(100), GETDATE(), 0): 05 16 2006 10:57AM
Select CONVERT(varchar(100), GETDATE(), 1): 05/16/06
Select CONVERT(varchar(100), GETDATE(), 2): 06.05.16
Select CONVERT(varchar(100), GETDATE(), 3): 16/05/06
Select CONVERT(varchar(100), GETDATE(), 4): 16.05.06
Select CONVERT(varchar(100), GETDATE(), 5): 16-05-06
Select CONVERT(varchar(100), GETDATE(), 6): 16 05 06
Select CONVERT(varchar(100), GETDATE(), 7): 05 16, 06
Select CONVERT(varchar(100), GETDATE(), 8): 10:57:46
Select CONVERT(varchar(100), GETDATE(), 9): 05 16 2006 10:57:46:827AM
Select CONVERT(varchar(100), GETDATE(), 10): 05-16-06
Select CONVERT(varchar(100), GETDATE(), 11): 06/05/16
Select CONVERT(varchar(100), GETDATE(), 12): 060516
Select CONVERT(varchar(100), GETDATE(), 13): 16 05 2006 10:57:46:937
Select CONVERT(varchar(100), GETDATE(), 14): 10:57:46:967
Select CONVERT(varchar(100), GETDATE(), 20): 2006-05-16 10:57:47
Select CONVERT(varchar(100), GETDATE(), 21): 2006-05-16 10:57:47.157
Select CONVERT(varchar(100), GETDATE(), 22): 05/16/06 10:57:47 AM
Select CONVERT(varchar(100), GETDATE(), 23): 2006-05-16
Select CONVERT(varchar(100), GETDATE(), 24): 10:57:47
Select CONVERT(varchar(100), GETDATE(), 25): 2006-05-16 10:57:47.250
Select CONVERT(varchar(100), GETDATE(), 100): 05 16 2006 10:57AM
Select CONVERT(varchar(100), GETDATE(), 101): 05/16/2006
Select CONVERT(varchar(100), GETDATE(), 102): 2006.05.16
Select CONVERT(varchar(100), GETDATE(), 103): 16/05/2006
Select CONVERT(varchar(100), GETDATE(), 104): 16.05.2006
Select CONVERT(varchar(100), GETDATE(), 105): 16-05-2006
Select CONVERT(varchar(100), GETDATE(), 106): 16 05 2006
Select CONVERT(varchar(100), GETDATE(), 107): 05 16, 2006
Select CONVERT(varchar(100), GETDATE(), 108): 10:57:49
Select CONVERT(varchar(100), GETDATE(), 109): 05 16 2006 10:57:49:437AM
Select CONVERT(varchar(100), GETDATE(), 110): 05-16-2006
Select CONVERT(varchar(100), GETDATE(), 111): 2006/05/16
Select CONVERT(varchar(100), GETDATE(), 112): 20060516
Select CONVERT(varchar(100), GETDATE(), 113): 16 05 2006 10:57:49:513
Select CONVERT(varchar(100), GETDATE(), 114): 10:57:49:547
Select CONVERT(varchar(100), GETDATE(), 120): 2006-05-16 10:57:49
Select CONVERT(varchar(100), GETDATE(), 121): 2006-05-16 10:57:49.700
Select CONVERT(varchar(100), GETDATE(), 126): 2006-05-16T10:57:49.827
Select CONVERT(varchar(100), GETDATE(), 130): 18 ???? ?????? 1427 10:57:49:907AM
Select CONVERT(varchar(100), GETDATE(), 131): 18/04/1427 10:57:49:920AM
常用：
Select CONVERT(varchar(100), GETDATE(), 8): 10:57:46
Select CONVERT(varchar(100), GETDATE(), 24): 10:57:47
Select CONVERT(varchar(100), GETDATE(), 108): 10:57:49
Select CONVERT(varchar(100), GETDATE(), 12): 060516
Select CONVERT(varchar(100), GETDATE(), 23): 2006-05-16
相关推荐
《Microsoft SQL Server 2005技术内幕：T-SQL查询》是一本深入解析SQL Server 2005中T-SQL查询语言的专业书籍。T-SQL（Transact-SQL）是微软对标准SQL的扩展，是SQL Server的核心组件，用于数据库管理、数据查询和...
本话题将详细讲解如何利用Flink的SQL Server Change Data Capture (CDC) 连接器版本2.3.0，将SQL Server中的数据实时同步到MySQL数据库。 首先，让我们了解什么是CDC。CDC是一种数据库技术，它能够捕获数据库中的...
### Microsoft Press Inside Microsoft SQL Server 2008: T-SQL Programming #### 一、书籍概览 本书《Microsoft Press Inside Microsoft SQL Server 2008: T-SQL Programming》是一本详细介绍Microsoft SQL ...
其他命令是 T-SQL 程序设计中的辅助命令，有多种命令，如 BACKUP、CHECKPOINT、DBCC、DECLARE、EXECUTE、KILL、PRINT、RAISERROR、READTEXT、RESTORE、SELECT、SET、SHUTDOWN 和 WRITETEXT 等。这些命令可以实现...
T-SQL是Microsoft SQL Server的核心组成部分，用于数据查询、更新、插入、删除以及更复杂的数据库操作。 本书全面覆盖了T-SQL的基础和高级概念，包括但不限于以下知识点： 1. **基础语法**：介绍T-SQL的基本结构，...
Sybase T-SQL（Transact-SQL）是Sybase Adaptive Server Enterprise (ASE)数据库管理系统中使用的编程语言，它是SQL标准的扩展，增加了许多特定于Sybase的功能。在深入探讨Sybase T-SQL命令之前，先要理解其基本概念...
在提供的文件列表中，"Microsoft.Press.Inside.Microsoft.SQL.Server.2005.T-SQL.Programming.Jun.2006.chm"很可能是一个详细的T-SQL编程指南，可能涵盖了上述所有概念以及更深入的细节。"file_id.diz"、"nf-2004....
《SQL 2000中的T-SQL参考手册》是一份专门为SQL Server 2000用户准备的技术文档，旨在帮助读者深入理解和掌握T-SQL（Transact-SQL）语言，它是SQL Server数据库管理系统中用于数据操作、查询、存储过程创建和数据库...
T-SQL是Microsoft SQL Server数据库管理系统中用于数据管理和操作的主要语言，对于任何想要在SQL Server环境中工作的开发者来说，掌握T-SQL是至关重要的。 本书旨在帮助新手以集合的观点来理解和解决问题，这是T-...
在SQL的世界中，T-SQL（Transact-SQL）是微软SQL Server数据库管理系统中使用的扩展SQL语言。它在标准SQL的基础上增加了一系列特性和功能，使得开发者能够更加灵活和高效地操作数据。下面将详细探讨T-SQL中的关键...
在IT行业中，数据库管理系统是至关重要的组成部分，而SQL Server作为微软公司推出的强大关系型数据库管理系统，广泛应用于数据存储、分析和处理。本文将详细讲解如何使用Visual Studio进行SQL Server的连接，以实现...
T-SQL语言是SQL Server数据库管理系统中使用的编程语言，用于编写数据库应用程序和存储过程。下面是T-SQL语言的主要知识点： 批处理 批处理是T-SQL语言中的一种机制，用于将多条SQL语句分隔成一个逻辑单元，以提高...
T-SQL，全称Transact-SQL，是SQL（Structured Query Language）的一种扩展，主要用于Microsoft的SQL Server数据库管理系统。T-SQL提供了丰富的语法和功能，用于处理和管理数据库中的数据，执行复杂的数据库操作，...
T-SQL语言是SQL Server数据库系统中用于执行数据库操作的脚本语言，它能够实现强大的数据处理功能，包括数据查询、数据操作、数据定义以及数据控制。在本文中，T-SQL语言的核心应用是用户文件的识别与检索，这个过程...
《Microsoft SQL Server 2005技术内幕：T-SQL程序设计》是一本深度解析SQL Server 2005数据库管理系统中T-SQL语言编程的权威指南。这本书由业界专家撰写，是Inside Microsoft SQL Server 2005系列的第一本，被誉为...
**Transact-SQL**，简称T-SQL，是SQL Server所使用的一种数据库查询和编程语言，它是标准SQL的扩展，增加了许多特性和功能，使得它更适合于微软的数据库管理系统。这个压缩包`Transact-SQL.rar`包含了丰富的T-SQL...
Visual Basic（VB）是一种流行的编程语言，而SQL Server则是微软公司推出的强大关系型数据库管理系统。在"VB-SQL-Server.rar_vb SQL Server"这个压缩包中，包含了关于如何使用Visual Basic连接和操作SQL Server...
### Sybase Adaptive Server Enterprise (ASE)：T-SQL 查询与数据库管理详解 #### 一、Sybase ASE 概述 **Sybase Adaptive Server Enterprise (ASE)** 是一款高性能的关系型数据库管理系统，由Sybase公司开发。它...
《Transact-SQL 语言参考大全》是一本深入探讨数据库编程的重要参考资料，主要针对SQL Server中的Transact-SQL（T-SQL）语法进行详尽解析。T-SQL是SQL Server的核心语言，用于创建、修改和操作数据库对象，执行查询...