CODEWITHSUNDEEP
sqlsql-servert-sql
Larry
Larry

Reputation: 2794

string ( dd/mm/yyyy) to date sql server

EDIT: The solution is in the reference post's solution .I was careless to overlook DATETIME--> Varchar(10)

`Syntax for CONVERT:

CONVERT ( data_type [ ( length ) ] , expression [ , style ] )`

I am aware of this post

SQL Server (2005, 2000, 7.0) does not have any flexible, or even non-flexible, way of taking an arbitrarily structured datetime in string format and converting it to the datetime data type.

So I am looking for a solution that solves this particular String format only.

Let's say I have a table in sql server with field :inputDate in datetime format

The following code works without convert/cast 

SELECT inputDate
FROM   some_table
WHERE  inputDate > '01/24/2013'

But it won't work for 

SELECT inputDate
FROM   some_table
WHERE  inputDate > '24/01/2013'

Throwing an The conversion of a varchar data type to a datetime data type resulted in an out-of-range value

Then I tried 

SELECT inputDate
FROM   some_table
WHERE  inputDate > CONVERT(VARCHAR(10), '24/01/2013', 103)

Throwing the same error

Is there a way to convert string in dd/mm/yyyy to be recognize as datetime format in SQL SERVER? Or the only way, and the proper way is doing santization elsewhere?

Upvotes: 4

Views: 13136

Answers (4)

goli55
goli55

Reputation: 81

Try to use the information in this post : ISO 8601

I succeed to do the following :

select convert(datetime,convert(char(23),'20140125' ,127),103) as MyDate

to get this : 2014-01-25 00:00:00.000

Upvotes: 0

cha
cha

Reputation: 10411

Use ISO 8601 date format YYYY-MM-DDT00:00:00. It will be implicitly converted to datetime in any locale

Upvotes: -1

Mark
Mark

Reputation: 8431

Try to increase the length of your VARCHAR(10) to VARCHAR(14) like:

select inputDate from Table 
where inputDate > convert(varchar(14), '24/01/2013', 103)

Upvotes: -2

John Woo
John Woo

Reputation: 263683

have you tried using DATETIME instead of VARCHAR(10)

WHERE inputDate > CONVERT(DATETIME, '24/01/2013', 103)

Upvotes: 8

Related Questions