Convert NVARCHAR to DATETIME in SQL Server 2008

In my table

LoginDate 2013-08-29 13:55:48 

The loginDate column's datatype is nvarchar(150)

I want to convert the logindate column into date time format using SQL command

Expected result.

LoginDate 29-08-2013 13:55:48 

6 Answers

DECLARE @chr nvarchar(50) = (SELECT CONVERT(nvarchar(50), GETDATE(), 103)) SELECT @chr chars, CONVERT(date, @chr, 103) date_again 

As your data is nvarchar there is no guarantee it will convert to datetime (as it may hold invalid date/time information) - so a way to handle this is to use ISDATE which I would use within a cross apply. (Cross apply results are reusable hence making is easier for the output formats.)

| YOUR_DT | SQL2008 | |-----------------------------|---------------------| | 2013-08-29 13:55:48 | 29-08-2013 13:55:48 | | 2013-08-29 13:55:48 blah | (null) | | 2013-08-29 13:55:48 rubbish | (null) | SELECT [Your_Dt] , convert(varchar, ca1.dt_converted ,105) + ' ' + convert(varchar, ca1.dt_converted ,8) AS sql2008 FROM your_table CROSS apply ( SELECT CASE WHEN isdate([Your_Dt]) = 1 THEN convert(datetime,[Your_Dt]) ELSE NULL END ) AS ca1 (dt_converted) ; 

Notes:

You could also introduce left([Your_Dt],19) to only get a string like '2013-08-29 13:55:48' from '2013-08-29 13:55:48 rubbish'

For that specific output I think you will need 2 sql 2008 date styles (105 & 8) sql2012 added for comparison

declare @your_dt as datetime2 set @your_dt = '2013-08-29 13:55:48' select FORMAT(@your_dt, 'dd-MM-yyyy H:m:s') as sql2012 , convert(varchar, @your_dt ,105) + ' ' + convert(varchar, @your_dt ,8) as sql2008 | SQL2012 | SQL2008 | |---------------------|---------------------| | 29-08-2013 13:55:48 | 29-08-2013 13:55:48 | 

alter table your_table alter column LoginDate datetime; 

SQLFiddle demo

SELECT CONVERT(NVARCHAR, LoginDate, 105)+' '+CONVERT(NVARCHAR, LoginDate, 108) AS LoginDate FROM YourTable Output ------------------- 29-08-2013 13:55:48 

What you exactly wan't to do ?. To change Datatype of column you can simple use alter command as

ALTER TABLE table_name ALTER COLUMN LoginDate DateTime;

But remember there should valid Date only in this column however data-type is nvarchar.

If you wan't to convert data type while fetching data then you can use CONVERT function as,

CONVERT(data_type(length),expression,style)

eg:

SELECT CONVERT(DateTime, loginDate, 6)

This will return 29 AUG 13. For details about CONVERT function you can visit ,

.

Remember, Always use DataTime data type for DateTime column.

Thank You

what about this

--// Convert NVARCHAR to DATETIME DECLARE @date DATETIME = (SELECT convert(DATETIME, '2013-08-29 13:55:48', 120)) --// Convert DATETIME to custom NVARCHAR FORMAT SELECT RIGHT('00'+ CAST(DAY(@date) AS NVARCHAR),2) + '-' + RIGHT('00'+ CAST(MONTH(@date) AS NVARCHAR),2) + '-' + CAST(YEAR(@date) AS NVARCHAR) + ' ' + CAST(CONVERT(TIME,@date) AS NVARCHAR) 

result: '29-08-2013 13:55:48.0000000'