SQL Server Convert Varchar to Datetime

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)

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/

Converting varchar date field to datetime datatype

Provided that all your data is in the format yyyy-MM-ddThh:mm:ss.nnnnnnn then you can just change the data type of the column:

ALTER TABLE dbo.YourTABLE ALTER COLUMN YourColumn datetime2(7);

If you need the timezone in there, then use datetimeoffset(7) instead.

SQL Server Convert Varchar to Datetime and Find Max Value

You can try the below way -

select max(cast(concat(columnName,'-01') as date))
from yourtable

Convert varchar column to datetime in sql server

First, if your table column is "DateTime" type than it will save data in this format "2014-10-09 00:00:00.000" no matter you convert it to date or not. But if not so and if you have SQL Server version 2008 or above than you can use this,

DECLARE @data nvarchar(50)
SET @data =  '10/9/2014'

IF(ISDATE(@data)>0)
BEGIN
    SELECT CONVERT(DATE, @data)
END

Otherwise

DECLARE @data nvarchar(50)
SET @data =  '10/9/2014'

IF(ISDATE(@data)>0)
BEGIN
    SELECT CONVERT(DATETIME, @data)
END

To Insert into table

INSERT INTO dbo.YourTable
SELECT CREATEDATE FROM
(
    SELECT
        (CASE WHEN (ISDATE(@data) > 0) THEN CONVERT(DATE, CREATEDATE) 
        ELSE CONVERT(DATE, '01/01/1900') END) as CREATEDATE 
    FROM 
        [dbo].[TestTB]
) AS Temp
WHERE
    CREATEDATE <> CONVERT(DATE, '01/01/1900')

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:

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