CODEWITHSUNDEEP
sqlsql-serverreporting-servicesssrs-2012
drewdaff
drewdaff

Reputation: 5

Converting datetime to varchar in SQL Stored Procedure

I have a stored procedure that is referencing column name's CycleStartedOn and CycleEndedOn. Both of those columns are datetime datatypes. My goal is to convert them to a varchar(MAX). I have variables @CBFCycleStart and @CBFCycleEnd holding those values. The code I have compiles but I am unsure if I am doing this incorrectly or if there is a more efficient and better way to achieve this goal. Here is the code I am working with:

DECLARE @CBFCycleStart as varchar(MAX)
DECLARE @CBFCycleEnd as varchar(MAX)

SELECT   
 @CBFCycleStart = (SELECT TOP 1 CONVERT(varchar(MAX), [CycleStartedOn], 120) FROM [IPEC_P_CIP_TKB_PREFLT] WHERE [CycleComplete] = '1' ORDER BY [CycleStartedOn] DESC)
 ,@CBFCycleEnd = (SELECT TOP 1 CONVERT(varchar(MAX), [CycleEndedOn], 120) FROM [IPEC_P_CIP_TKB_PREFLT] WHERE [CycleComplete] = '1' ORDER BY [CycleStartedOn] DESC)


 DECLARE @tblReports TABLE
(    
    CBFCycleStart varchar(MAX)
    ,CBFCycleEnd varchar(MAX)
)

INSERT INTO @tblReports
(    
    CBFCycleStart 
    ,CBFCycleEnd 
)

Values
(   
    @CBFCycleStart
    ,@CBFCycleEnd
)

SELECT * FROM @tblReports

END

Upvotes: 0

Views: 455

Answers (2)

Serg
Serg

Reputation: 22811

You can spare one select as both select the same row

SELECT TOP 1  @CBFCycleStart = CONVERT(varchar(MAX), [CycleStartedOn], 120) 
 ,@CBFCycleEnd = CONVERT(varchar(MAX), [CycleEndedOn], 120) 
 FROM [IPEC_P_CIP_TKB_PREFLT] 
 WHERE [CycleComplete] = '1' 
 ORDER BY [CycleStartedOn] DESC;

Upvotes: 1

Chris Mack
Chris Mack

Reputation: 5208

You could simplify it a bit like this:

DECLARE @tblReports TABLE
(    
    CBFCycleStart varchar(MAX)
    , CBFCycleEnd varchar(MAX)
)

INSERT INTO @tblReports
(    
    CBFCycleStart 
    , CBFCycleEnd 
)

SELECT TOP 1
    CONVERT(varchar(MAX), [CycleStartedOn], 120)
    , CONVERT(varchar(MAX), [CycleEndedOn], 120)
FROM [IPEC_P_CIP_TKB_PREFLT]
WHERE [CycleComplete] = '1'
ORDER BY [CycleStartedOn] DESC

SELECT * FROM @tblReports

It also provides a little more certainty that you're always selecting from the same row.

Edit

If you need the variables after the INSERT, you could use:

DECLARE @CBFCycleStart as varchar(MAX)
DECLARE @CBFCycleEnd as varchar(MAX)

SELECT TOP 1
    @CBFCycleStart = CONVERT(varchar(MAX), [CycleStartedOn], 120)
    , @CBFCycleEnd = CONVERT(varchar(MAX), [CycleEndedOn], 120)
FROM [IPEC_P_CIP_TKB_PREFLT]
WHERE [CycleComplete] = '1'
ORDER BY [CycleStartedOn] DESC

DECLARE @tblReports TABLE
(    
    CBFCycleStart varchar(MAX)
    , CBFCycleEnd varchar(MAX)
)

INSERT INTO @tblReports
(    
    CBFCycleStart 
    , CBFCycleEnd 
)

VALUES
(
    @CBFCycleStart
    , @CBFCycleEnd
)

SELECT * FROM @tblReports

Upvotes: 1

Related Questions