CODEWITHSUNDEEP
sqlsql-serverdate
rahul16590
rahul16590

Reputation: 401

Conversion of varchar to date type in SQL

I am trying to convert varchar field which is in format dd.mm.yyyy to date type field in SQL SERVER 2008.

I used approach like convert(datetime, left(yourDateField, 2) + '01 20' + right(yourDateField, 4), 100) but unable to so.

Is there any way to convert Varchar string which is in format dd.mm.yyyy to Date type in SQL?

Upvotes: 0

Views: 109

Answers (4)

mansi
mansi

Reputation: 877

If Your date field is not in proper datetime format then this type of Out of range value error will occurred.

If your date field is in proper datetime format then you can easily convert varchar field to datetime format.

see example:

select CONVERT(datetime,'2016-11-21 01:02:12') 
select CONVERT(datetime,'2016-11-31 01:02:12') 
select CONVERT(datetime,'2016-11-21 00:00:01 PM')

In above Example,First query get easily converted to datetime but second and third query will give error as out of range value. because 31 Nov is invalid date. and 00:00:00 PM is also wrong date format.

IF your field is in correct format then simply try any one of the below query

DECLARE @String VARCHAR(100)= '21.11.2016';
SELECT CONVERT(DATETIME,@String,104)
SELECT CONVERT(DATETIME,@String,103)

Hope so, you get what I am trying to say.

Upvotes: 0

Mansoor
Mansoor

Reputation: 4192

Try this.

DECLARE @String VARCHAR(100)= '21.11.2016';
SELECT CONVERT(DATE,@String,103)

Upvotes: 0

Guruprakash Chinnasamy
Guruprakash Chinnasamy

Reputation: 144

Try this Datetime to Varchar

SELECT CONVERT(VARCHAR(20),GETDATE(),104)

Varchar to Datetime 

SELECT CONVERT(DATETIME,yourfeild,104)

Refer this link

Upvotes: 0

Akshey Bhat
Akshey Bhat

Reputation: 8545

select convert(date,dateField,104)
from tab

Upvotes: 2

Related Questions