SQL Server Convert String to Datetime

Sql Server string to date conversion

Try this

Cast('7/7/2011' as datetime)

and

Convert(DATETIME, '7/7/2011', 101)

See CAST and CONVERT (Transact-SQL) for more details.

SQL server - converting string to datetime

Please try the below:

select convert(datetime, substring(replace(replace('08-JAN-19 09.35.58.173000000', '-', ' '), '.', ':'), 1, 18))

Before you can convert a string to datetime, your string must conform to certain patterns as shown in https://docs.microsoft.com/en-us/sql/t-sql/data-types/datetime-transact-sql?view=sql-server-ver15

How to convert string to datetime without seperator using sql?

You can't convert these patterned string to datetime directly and i don't see any use of CONVERT function for this purpose. You can just format the string like below to cast it :

DECLARE @date VARCHAR(MAX) = '20180227105954636241'
DECLARE @date1 VARCHAR(30) 

set @date1 = SUBSTRING(@date,  1, 8) + ' ' +  -- this is datepart
             SUBSTRING(@date,  9, 2) + ':' +  -- this is hour
             SUBSTRING(@date, 11, 2) + ':' +  -- this is minute
             SUBSTRING(@date, 13, 2) + '.' +  -- this is second
             SUBSTRING(@date, 15, 6)          -- this is decimal of second

select cast(@date1 as datetime2(6))  -- 6 is the decimal point of second

Output

2018-02-27 10:59:54.636241

Converting a string to a datetime in MS SQL Server

CONVERT needs the appropriate parameter to know how to parse a date string.

Look at this link to find details.

Your case needs 101:

select CONVERT(datetime, '12/16/2001 11:00:00 PM', 101)

If this is under your control, you should avoid any culture related date literal. Best for DateTime - if you have to type in a date with time - is ODBC

SELECT {ts'2001-12-16 23:00:00'};

Or ISO 8601

SELECT CONVERT(DATETIME,'2001-12-16T23:00:00',126);

Here's a related answer

And here's another one

How to convert string to datetime in SQL

Found a way to do it in SQL Server:

select cast(right("string",20) as datetime) as dateColumn
from table

SQL Server convert string to datetime

For instance you can use

update tablename set datetimefield='19980223 14:23:05'
update tablename set datetimefield='02/23/1998 14:23:05'
update tablename set datetimefield='1998-12-23 14:23:05'
update tablename set datetimefield='23 February 1998 14:23:05'
update tablename set datetimefield='1998-02-23T14:23:05'

You need to be careful of day/month order since this will be language dependent when the year is not specified first. If you specify the year first then there is no problem; date order will always be year-month-day.

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]

String to datetime conversion in sql server

If we assume that the value is always in the format MMM-yy then you could do this:

SELECT CONVERT(date,'01-' + StringDate,106)
FROM dbo.YourTable;

Of course, this has 2 flaws.

1. The date uses a 2 digit year, so SQL Server could assume the wrong century
2. It'll only work if the LOGIN is using an English based language, otherwise it'll fail.

The real solution is to fix your design; never store date (and time) values in a varchar, and when ever you do use a varchar for a date (such as a literal in a WHERE) use an unambiguous format such as yyyyMMdd or yyyy-MM-ddThh:mm:ss.nnnnnnn.

---

Related Topics