6

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
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
user2617053
  • 323
  • 3
  • 5
  • 13

6 Answers6

7
DECLARE @chr nvarchar(50) = (SELECT CONVERT(nvarchar(50), GETDATE(), 103))

SELECT @chr chars, CONVERT(date, @chr, 103) date_again
3

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 |
Paul Maxwell
  • 33,002
  • 3
  • 32
  • 51
1
alter table your_table
alter column LoginDate datetime;

SQLFiddle demo

juergen d
  • 201,996
  • 37
  • 293
  • 362
1
SELECT CONVERT(NVARCHAR, LoginDate, 105)+' '+CONVERT(NVARCHAR, LoginDate, 108) AS LoginDate FROM YourTable

Output
-------------------
29-08-2013 13:55:48
Nisha
  • 1,379
  • 16
  • 28
  • Incorrect. The OP is asking how to convert NVARCHAR *to* a date value, whereas you are showing how to convert a DATE *to* a NVARCHAR. – Richard Woolf May 28 '21 at 00:03
0

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 ,

http://www.w3schools.com/sql/func_convert.asp.

Remember, Always use DataTime data type for DateTime column.

Thank You

Regon
  • 392
  • 1
  • 4
  • 17
  • I don't have access to alter the column. I want to format the output – user2617053 Oct 07 '13 at 07:49
  • Then You can use CONVERT function.You can use 3 or 113 instead of 6 in above example for your required date format in above questions. – Regon Oct 07 '13 at 07:59
0

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'

Muflix
  • 6,192
  • 17
  • 77
  • 153