-----=========================SQL时间集合=================================
--时间yyyyMMddHHmmss<-DTMPROCESSED(2013-02-23 10:43:01.837)
select top 2 convert(varchar,DTMPROCESSED,112) +replace(convert(varchar,DTMPROCESSED ,108), ':', '')
FROM DAILYSALES
--时间MMdd<-GETDATE()
DECLARE @TODAY VARCHAR(10);
SELECT @TODAY = RIGHT(CONVERT( VARCHAR(10),GETDATE(),112),4)
SELECT @TODAY ,GETDATE()
--时间YYYYMMdd <-'2013-02-07 18:14:03.000'
SET @TODAY = '2013-02-07 18:14:03.000'
SET @TODAY =SUBSTRING(@TODAY,1,4)+ SUBSTRING(@TODAY,6,2)+ SUBSTRING(@TODAY,9,2)
SELECT @TODAY
--时间YYYYMMDDHHMMSS,YYYYMMDD,YYYY-MM-DD HH:MM:SS
--1.YYYYMMDDHHMMSS<-@today(varchar)
select SUBSTRING(@TODAY,1,4)+ SUBSTRING(@TODAY,6,2)+ SUBSTRING(@TODAY,9,2) + SUBSTRING(@TODAY,12,2)+ SUBSTRING(@TODAY,15,2) + SUBSTRING(@TODAY,18,2)
--结果:20130128194907
--2. YYYYMMDD<- DTMPROCESSED
convert(varchar(30),DTMPROCESSED,112)
--结果:20130128
--3. YYYY-MM-DD HH:MM:SS <- DTMPROCESSED
convert(varchar(30),DTMPROCESSED,120)
--结果:2013-01-28 17:47:57
DECLARE @TODAY VARCHAR(25);SELECT @TODAY = CONVERT( VARCHAR(25), GETDATE() ,120)
--TODAY 2013-04-02 17:35:40
--4. YYYYMMDDHHMMSS <-DTMPROCESSED[datetime]->
select convert(varchar(30),DTMPROCESSED,112)+replace(right(convert(varchar(40),DTMPROCESSED,120),8),':','') )as salesdate from DAILYSALES where DTMPROCESSED >= '20130128'
--结果:20130128174757(原数据2013-01-28 17:47:57.087)
--5. YYYYMMDDHHMMSS, YYYYMMDD + HHMMSS<- getdate()[DATETIME]
select convert(varchar(30),GETDATE(),112)+replace(right(convert(varchar(40),GETDATE(),120),8),':','')
--结果:20130402173854(原数据2013-04-02 17:39:29.090)
select convert(varchar(30),GETDATE(),112) AS 年月日,
replace(right(convert(varchar(40),GETDATE(),120),8),':','')AS 时间
--时间yyyyMMddHHmmss<-DTMPROCESSED(2013-02-23 10:43:01.837)
select top 2 convert(varchar,DTMPROCESSED,112) +replace(convert(varchar,DTMPROCESSED ,108), ':', '')
FROM DAILYSALES
--时间MMdd<-GETDATE()
DECLARE @TODAY VARCHAR(10);
SELECT @TODAY = RIGHT(CONVERT( VARCHAR(10),GETDATE(),112),4)
SELECT @TODAY ,GETDATE()
--时间YYYYMMdd <-'2013-02-07 18:14:03.000'
SET @TODAY = '2013-02-07 18:14:03.000'
SET @TODAY =SUBSTRING(@TODAY,1,4)+ SUBSTRING(@TODAY,6,2)+ SUBSTRING(@TODAY,9,2)
SELECT @TODAY
--时间YYYYMMDDHHMMSS,YYYYMMDD,YYYY-MM-DD HH:MM:SS
--1.YYYYMMDDHHMMSS<-@today(varchar)
select SUBSTRING(@TODAY,1,4)+ SUBSTRING(@TODAY,6,2)+ SUBSTRING(@TODAY,9,2) + SUBSTRING(@TODAY,12,2)+ SUBSTRING(@TODAY,15,2) + SUBSTRING(@TODAY,18,2)
--结果:20130128194907
--2. YYYYMMDD<- DTMPROCESSED
convert(varchar(30),DTMPROCESSED,112)
--结果:20130128
--3. YYYY-MM-DD HH:MM:SS <- DTMPROCESSED
convert(varchar(30),DTMPROCESSED,120)
--结果:2013-01-28 17:47:57
DECLARE @TODAY VARCHAR(25);SELECT @TODAY = CONVERT( VARCHAR(25), GETDATE() ,120)
--TODAY 2013-04-02 17:35:40
--4. YYYYMMDDHHMMSS <-DTMPROCESSED[datetime]->
select convert(varchar(30),DTMPROCESSED,112)+replace(right(convert(varchar(40),DTMPROCESSED,120),8),':','') )as salesdate from DAILYSALES where DTMPROCESSED >= '20130128'
--结果:20130128174757(原数据2013-01-28 17:47:57.087)
--5. YYYYMMDDHHMMSS, YYYYMMDD + HHMMSS<- getdate()[DATETIME]
select convert(varchar(30),GETDATE(),112)+replace(right(convert(varchar(40),GETDATE(),120),8),':','')
--结果:20130402173854(原数据2013-04-02 17:39:29.090)
select convert(varchar(30),GETDATE(),112) AS 年月日,
replace(right(convert(varchar(40),GETDATE(),120),8),':','')AS 时间