在存储过程日期格式中要更改ST_DATE [英] In stored procedure date format to be changed ST_DATE

问题描述

我的商店程序如下

My store procedure as follows

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROC [dbo].[OH_PKG_DATE_COMBINATION](@PKG_ID VARCHAR(50))

AS
BEGIN

DECLARE @SNO INT
DECLARE @SNO_I INT
DECLARE @COURSE VARCHAR(50),@COURSE_I VARCHAR(50),@ST_DATE DATETIME,@EN_DATE DATETIME,@DISP VARCHAR(100)
DECLARE @EN_DATE_I INT
DECLARE @COURSE_COUNT INT , @COURSE_COUNT_I INT
DECLARE @EN_DATE_J DATETIME
DECLARE @TEST VARCHAR(50)
CREATE TABLE #TEMP (CMN_MINOR_CODE VARCHAR(50),ST_DATE DATETIME,EN_DATE DATETIME,DISP VARCHAR(100))
CREATE TABLE #TEMP_ALLOCATION (SNO INT IDENTITY,COURSE VARCHAR(50),ST_DATE DATETIME,EN_DATE DATETIME,DISP VARCHAR(100))
CREATE TABLE #TEMP_ALLOCATION_COURSES (SNO VARCHAR(50),COURSE VARCHAR(50),ST_DATE VARCHAR(50),EN_DATE VARCHAR(50),DISP VARCHAR(100),SNO1 INT IDENTITY)
CREATE TABLE #TEMP_SNO (SNO INT)
CREATE TABLE #Temp_Result (SNO VARCHAR(50),COURSE VARCHAR(50),ST_DATE VARCHAR(50),EN_DATE VARCHAR(50),DISP VARCHAR(100),SNO1 INT )

INSERT INTO #TEMP
Select Distinct
d.cmn_minor_code ,
b.cbm_batch_start_dt ,
cbm_batch_end_dt ,
(rtrim(d.cmn_minor_code) + ' : Date - '
+ convert(varchar,b.cbm_batch_start_dt,106))
+ ' To - ' + CONVERT(VARCHAR, cbm_batch_end_dt,106)
 
From
co_package_detail D,
co_batch_master B,
batch_seats S
Where
s.cbm_batch_id = b.cbm_batch_id And
s.avil_Seats > 0 And
b.cbm_batch_start_dt > getdate()- 1 And
b.cbm_batch_start_dt < getdate()+ 5000 And
b.cmn_minor_code = d.cmn_minor_code And
d.cpm_pkg_id =@PKG_ID
Order By 2
SELECT @COURSE_COUNT = COUNT(*) FROM CO_PACKAGE_DETAIL WHERE cpm_pkg_id = @PKG_ID
INSERT INTO #TEMP_ALLOCATION(COURSE,ST_DATE,EN_DATE,DISP) SELECT * FROM #TEMP
INSERT INTO #TEMP_SNO SELECT SNO FROM #TEMP_ALLOCATION
DECLARE CUR_COURSES CURSOR FOR
SELECT * FROM #TEMP_ALLOCATION order by 1
OPEN CUR_COURSES
FETCH CUR_COURSES INTO @SNO,@COURSE,@ST_DATE,@EN_DATE,@DISP
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO #TEMP_ALLOCATION_COURSES VALUES (@SNO,@COURSE,@ST_DATE,@EN_DATE,@DISP)
SET @SNO_I = @SNO
WHILE @SNO_I > 0
BEGIN
SELECT @COURSE_COUNT_I = COUNT(*) FROM #TEMP_ALLOCATION_COURSES WHERE SNO = @SNO_I AND COURSE=@COURSE
IF @COURSE_COUNT_I < 1
BEGIN
SELECT @EN_DATE_I = COUNT(*) FROM #TEMP_ALLOCATION
WHERE ST_DATE > (SELECT TOP 1 EN_DATE FROM #TEMP_ALLOCATION_COURSES WHERE SNO = @SNO_I) and SNO=@SNO --ORDER BY 1 DESC)
SELECT TOP 1 @TEST = (CONVERT(VARCHAR,EN_DATE) + RTRIM(COURSE) + CONVERT(VARCHAR,SNO)) FROM #TEMP_ALLOCATION_COURSES
WHERE SNO = @SNO_I ORDER BY 1 DESC
SELECT @TEST = (@TEST + ' - C - ' + CONVERT(VARCHAR,COUNT(*))) FROM #TEMP_ALLOCATION_COURSES
WHERE SNO = @SNO_I
SELECT TOP 1 @EN_DATE_J = EN_DATE FROM #TEMP_ALLOCATION_COURSES
WHERE SNO = @SNO_I ORDER BY 1 DESC
IF @EN_DATE_J<@ST_DATE
BEGIN
INSERT INTO #TEMP_ALLOCATION_COURSES
VALUES(
@SNO_I,@COURSE,@ST_DATE,@EN_DATE,@DISP )
END
END
SELECT @COURSE_COUNT_I=COUNT(*) FROM #TEMP_ALLOCATION_COURSES WHERE SNO=@SNO_I
IF @COURSE_COUNT_I=@COURSE_COUNT
BEGIN
DELETE FROM #TEMP_SNO WHERE SNO=@SNO_I
END
SET @SNO_I = @SNO_I - 1
END
FETCH CUR_COURSES INTO @SNO,@COURSE,@ST_DATE,@EN_DATE,@DISP
END
CLOSE CUR_COURSES
DEALLOCATE CUR_COURSES

Declare @Rcount int
Declare @Sno1 int
Set @Sno1=1;
Declare @Snoremove Varchar(max)
Set @Rcount=(Select distinct Sno from #TEMP_ALLOCATION_COURSES
WHERE SNO NOT IN( SELECT * FROM #TEMP_SNO) and Sno=@Sno1)

WHILE @Rcount <20
BEGIN
insert into #Temp_Result
Select SNO,COURSE,ST_DATE,EN_DATE,DISP,0
from #TEMP_ALLOCATION_COURSES WHERE SNO NOT IN( SELECT * FROM #TEMP_SNO) and Sno=@Rcount
union all
Select top 1 'ns' as sno,'sc' as COURSE,'sd 'as ST_DATE,'ed' as EN_DATE,'ds' as DISP,SNO1 from #TEMP_ALLOCATION_COURSES WHERE SNO NOT IN( SELECT * FROM #TEMP_SNO) and Sno=@Rcount
SET @Sno1 = @Sno1 + 1
SET @Rcount = @Rcount + 1;
END;

Set @Snoremove=(Select max(sno1) from #Temp_Result )
Delete from #Temp_Result where sno1=@Snoremove
Select * from #Temp_Result ;
END

当我运行上面的商店程序输出时如下



exec [OH_PKG_DATE_COMBINATION]'PKG0000005'

When i run the above store procedure output as follows

exec [OH_PKG_DATE_COMBINATION] 'PKG0000005'

  SNO    Course                     ST_DATE              EN_DATE                                          DISP                                                           SNO1
 
 
1      PSCRB               Jul 20 2015 12:00AM            Jul 24 2015 12:00AM           PSCRB : Date - 20 Jul 2015 To - 24 Jul 2015       0
1     AFF                      Jul 27 2015 12:00AM           Aug 1 2015 12:00AM           AFF : Date - 27 Jul 2015 To - 01 Aug 2015          0
1     MFA                     Jul 29 2015 12:00AM          Aug 1 2015 12:00AM            MFA : Date - 29 Jul 2015 To - 01 Aug 2015         0

从上面的存储过程我想要输出如下

From the above stored procedure i want output as follows

 SNO  Course                  ST_DATE                   EN_DATE                                             DISP                                                     SNO1 
 
1 PSCRB                 20 Jul   2015                          24 Jul 2015                      PSCRB : Date - 20 Jul 2015 To - 24 Jul 2015                0
1 AFF                      27 Jul 2015                            1   Aug 2015                     AFF : Date - 27 Jul 2015 To - 01 Aug 2015                  0
1 MFA                     29 Jul 2015                           1   Aug2015                      MFA : Date - 29 Jul 2015 To - 01 Aug 2015                  0

从存储过程如何将ST_DATE和EN_DATE更改为上述格式。例20 2015年7月。



请帮助我。

From the stored procedure how the change the ST_DATE and EN_DATE into above format. Example 20 Jul 2015.

please help me.

推荐答案

USE



SELECT转换（varchar，ST_DATE，106）





工作SELECT转换（varchar，getdate（），106）

USE

SELECT convert(varchar, ST_DATE, 106)


Working SELECT convert(varchar, getdate(), 106)

这篇关于在存储过程日期格式中要更改ST_DATE的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！