Convert Nvarchar to Datetime in SQL Server 2008

How to convert nvarchar to datetime in mssql

Date & Time Formats are stored as integer internally, it's the difference from the date '01/01/1900'. You can display & manipulate it into different formats, but unformatted select statement will show in "2018-05-18 11:09:31.000" this format only.

If you want to store in the specified format, you need to change the datetime column to Nvarchar in your table.

Use the Query for the format you specified.

SELECT Convert(VARCHAR, convert(datetime,@SESSION_START_TIME,105),105)+ ' ' +
RIGHT(Convert(VARCHAR, convert(datetime,@SESSION_START_TIME,105), 22) ,11)

Converting NVARCHAR to SMALLDATETIME on SQL SERVER 2008

Try add LEFT function in RIGHT to your query in case clause like:

CASE WHEN ISDATE(CreateDt1) = 1

THEN CAST(CreateDt1 AS datetime)

ELSE 

    CASE WHEN SUBSTRING(CreateDt1, 3, 1) = '/'
    THEN 
        CASE WHEN ISDATE(SUBSTRING(CreateDt1, 4, 2) + '/' + LEFT(CreateDt1, 2) + '/' + RIGHT (LEFT(CreateDt1,10), 4)) = 1
            THEN CAST(SUBSTRING(CreateDt1, 4, 2) + '/' + LEFT(CreateDt1 , 2) + '/' + RIGHT (LEFT(CreateDt1,10), 4) AS datetime)
        END
    END
END AS NewDate

CAST nvarchar to DATETIME

It looks like you need some string manipulation as your date string isn't in a recognized format.

There might be a simpler way, but this works in SQL Server:

DECLARE @string VARCHAR(255) = '20130515103000'
SELECT CAST(LEFT(@string,8)+' '+SUBSTRING(@string,9,2)+':'+SUBSTRING(@string,11,2)+'.'+RIGHT(@string,2) AS DATETIME)

Note, I'm assuming the format of your string is "yyyyMMDDHHMMSS" and using 24 hours since AM/PM is not indicated.

Update: The variable is just for testing, to implement it just replace the variable with your datetime string field:

SELECT CAST(LEFT(survey_date,8)+' '+SUBSTRING(survey_date,9,2)+':'+SUBSTRING(survey_date,11,2)+'.'+RIGHT(survey_date,2) AS DATETIME) AS Expr1

Failed conversion from nvarchar to datetime

I would do this in two parts, one for the date and one for the time:

SELECT title,link,s.sourceID,
       (CONVERT(DATETIME, SUBSTRING([date], 5, 10), 106) +
        CONVERT(DATETIME, SUBSTRING([date], 18, 8))
       )
FROM feed.tempXML t JOIN
     feed.[source] s
     ON s.sourceName = t.[source];

This minimizes the string operations, so it seems like a pretty simple approach.

---

Related Topics