How to Convert Datetime to Varchar

How to convert DateTime to VarChar

With Microsoft Sql Server:

--
-- Create test case
--
DECLARE @myDateTime DATETIME
SET @myDateTime = '2008-05-03'

--
-- Convert string
--
SELECT LEFT(CONVERT(VARCHAR, @myDateTime, 120), 10)

SQL convert datetime to varchar

This is something that would usually be done in the presentation layer, but if you need to do it in the data layer you can use FORMAT (documentation here)

Syntax is: FORMAT ( value, format [, culture ] )

So in your case (edited to add AM/PM designation per your comments): FORMAT([Date & Time Added], 'dd/MM/yyy hh:mm:ss.fff tt')

Custom date format string options are detailed here

Convert DATETIME to varchar in SQL

You want format for that e.g.

select format(current_timestamp, 'dddd dd-MMM-yy hh:mm tt');

Note: format doesn't perform as well as convert or cast but it has the added flexibility you need.

convert datetime to varchar(50)

this will work in sqlserver SQLFiddle regarding this Demo

SELECT convert(varchar, getdate(), 103) 
+' '+ CONVERT(varchar(15),CAST(getdate() AS TIME),100)

Conversion of datetime to varchar not working when passing through variables

If I guess correct, you are looking for 103 date time format (British/French) which is DD/MM/YYYY. That case you can try this below-

DECLARE @d DATETIME
SET @d =CONVERT(DATETIME, '31/12/2019 16:12:25.317',103)

SELECT @d

SELECT CONVERT(DATETIME, '31/12/2019 16:12:25.317', 103)

Converting datetime to varchar in SQL Stored Procedure

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

How to convert a datetime to string in T-SQL

The following query will get the current datetime and convert into string. with the following format
yyyy-mm-dd hh:mm:ss(24h)

SELECT convert(varchar(25), getdate(), 120) 

• SQLFiddle Demo
• SQL Server Date Formats

---

Related Topics