Convert string format dd/mm/yy to insert

[bernie321]

Hi

We have a string that has been output by a third party application which is a date with the format dd/mm/yy.

What is the best method of converting this for a safe insert.

Thanks

B

 

[vongrunt]

SET DATEFORMAT dmy

------
heisenbug: A bug that disappears or alters its behavior when one attempts to probe or isolate it
schroedinbug: A bug that doesn't appear until someone reads source code and realizes it never should have worked, at which point the program promptly stops working for everybody until fixed.

 

[bernie321]

Hi Vongrunt

I cannot run that as we also need to execute the SQL Statement from a 3rd party application.

It requires me to execute only a single line statement.

Thanks

B

 

[JamesLean]

If you can't set the dateformat the only option you have is to change the language of the login that is performing the update. It will be English by default which is US and so will interpret dates in M/D/Y format. Change it to British English and it will interpret as D/M/Y format.

--James

 

[bernie321]

Hi

This is also not an option as it will affect other areas of the system.

I would usually perform CONVERT(DATETIME, '20/12/2005', 103) but as this date is dd/mm/yy not dd/mm/yyyy.

I am wondering if there is a better method rather than adding '20' to the start of year of '20/12/05' to make it '20/12/2005' (as the date will be within a month of the input).

Thanks

B

 

[JamesLean]

If you haven't got the century on the value then you can use:

CONVERT(datetime, '20/12/05', 3)

--James