Convert Varchar into Datetime in SQL Server

SQL Server Convert Varchar to Datetime

SELECT CONVERT(Datetime, '2011-09-28 18:01:00', 120) -- to convert it to Datetime

SELECT CONVERT( VARCHAR(30), @date ,105) -- italian format [28-09-2011 18:01:00]
+ ' ' + SELECT CONVERT( VARCHAR(30), @date ,108 ) -- full date [with time/minutes/sec]

Convert varchar into datetime in SQL Server

OP wants mmddyy and a plain convert will not work for that:

select convert(datetime,'12312009')

Msg 242, Level 16, State 3, Line 1 
The conversion of a char data type to a datetime data type resulted in 
an out-of-range datetime value

so try this:

DECLARE @Date char(8)
set @Date='12312009'
SELECT CONVERT(datetime,RIGHT(@Date,4)+LEFT(@Date,2)+SUBSTRING(@Date,3,2))

OUTPUT:

-----------------------
2009-12-31 00:00:00.000

(1 row(s) affected)

Error converting varchar to Datetime in a SQL Function

Sample:

Create function [dbo].[getDateValue1](@input_date datetime2)  
returns varchar(100)  
begin  
    declare 
    @v_ret varchar(100)

    set @v_ret = CONVERT(varchar, @input_date, 112)
    return @v_ret
end 


SELECT dbo.getDateValue1('2022-01-01 18:15:15.600')
-- Result: 
20220101

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 varchar to datetime SQL Server

There are two possibilities:

SQL Server misinterprets the date time, because it thinks that it is in a different format. To fix this, use CONVERT with the optional third 'format' parameter.

CONVERT(DATE, TSTAMP, 103) -- 103 is dd/mm/yyyy

The other possibility is that the column contains a value which cannot be stored in the selected data type or makes no sense, such example would be '13/13/2000' which can in fact be stored in VARCHAR, but makes no sense as a DATE.

To catch these, you can try to use TRY_CONVERT(DATE, TSTAMP, 103) which will result in NULL where the date cannot be converted.

Further reading:
https://www.sqlshack.com/sql-server-functions-for-converting-string-to-date/

SQL Server : Convert from VARCHAR to DATETIME

I'm not sure that your current datetime literal falls into any mask which SQL Server can recognize. But we can try using TRY_CONVERT here, replacing the middle dash with a space:

SELECT TRY_CONVERT(datetime, STUFF(@input, 11, 1, ' ')) AS output;

18/12/2018 03:14:48

Demo

Edit:

If you are using an earlier version of SQL Server which does not support TRY_CONVERT, then we can try explicitly using CONVERT:

SELECT CONVERT(datetime, STUFF(@input, 11, 1, ' ')) AS output;

Convert varchar string to datetime

Steps:

1. Read the documentation for CONVERT and pick the closest format to use
2. Do some string manipulation to get the desired format
3. Convert.

DECLARE @col varchar(14) = '20220602235900';

SELECT
    CONVERT(date, SUBSTRING(@col,1,8), 121) [Date Component]
    , CONVERT(time, SUBSTRING(@col,9,2) + ':' + SUBSTRING(@col,11,2) + ':' + SUBSTRING(@col,13,2), 8) [Time Component]
    , CONVERT(datetime, SUBSTRING(@col,1,4) + '-' + SUBSTRING(@col,5,2) + '-' + SUBSTRING(@col,7,2) + ' ' + SUBSTRING(@col,9,2) + ':' + SUBSTRING(@col,11,2) + ':' + SUBSTRING(@col,13,2), 120) [DateTime Representation];

Returns:

UPDATE  Orders SET OrderDateTime=CAST(OrderDateTime AS datetime2)