4

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?

Community
  • 1
  • 1
Larry
  • 2,764
  • 2
  • 25
  • 36
  • The problem is you need to decide on one format or the other. How do you expect SQL Server to know if `07/08/2012` is July 8th or August 7th? – Aaron Bertrand Jan 24 '13 at 03:14
  • @AaronBertrand By using the 3rd agrument in convert function, I suppose. JW. solved this question. – Larry Jan 24 '13 at 03:18
  • 1
    I was referring to the previous post, which you referenced, where they seemed to want SQL Server to know whether to use 101 or 103 automatically. – Aaron Bertrand Jan 24 '13 at 03:20
  • Where does the string come from, and how is it sent to the server? I ask because I have in mind a few option that neatly side-step this hole problem. – Joel Coehoorn Jan 24 '13 at 03:24
  • @JoelCoehoorn From Excel, my solution was to use Format(date,"mm/dd/yyyy") , then using ADODB.connection – Larry Jan 24 '13 at 03:26

4 Answers4

8

have you tried using DATETIME instead of VARCHAR(10)

WHERE inputDate > CONVERT(DATETIME, '24/01/2013', 103)
John Woo
  • 258,903
  • 69
  • 498
  • 492
  • P.S. The solution is in the reference post's solution .I was careless to overlook DATETIME--> Varchar(10) – Larry Jan 24 '13 at 03:24
0

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

Community
  • 1
  • 1
goli55
  • 61
  • 1
  • 2
-1

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

cha
  • 10,301
  • 1
  • 18
  • 26
  • Thanks, that means I have to reformat my sql date string to yyyy-mm-dd in pre-processing? – Larry Jan 24 '13 at 03:12
  • 1
    No, this is not true! Try `SET LANGUAGE FRENCH; SELECT CONVERT(DATETIME, '2012-12-31');` – Aaron Bertrand Jan 24 '13 at 03:15
  • 1
    Check this MSDN Article: http://msdn.microsoft.com/en-us/library/ms187819.aspx. (search for ISO 8601) – cha Jan 24 '13 at 03:18
  • 1
    *sigh* Did you try what I posted? Did you notice that in the article the ISO 8601 format *includes time* and the `T` separator? If you tried what I posted you'd understand why I don't agree that YYYY-MM-DD is safe in any locale, in spite of what you might have interpreted from the MSDN article. – Aaron Bertrand Jan 24 '13 at 03:19
  • 1
    Why not suggest `YYYYMMDD` instead of that much more complex string? YYYYMMDD is universally safe unlike YYYY-MM-DD. – Aaron Bertrand Jan 24 '13 at 03:21
-2

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)
Mark
  • 8,046
  • 15
  • 48
  • 78