sql server convert string to datetime

Question

Hello, I have this string: '30/05/2010', and I would like to enter it to a smallDatetime field. In the database it should look something like this 2010-05-30 15:33:25 Any Idea how?

TY

Answer 1

You need to use the datetime field type if you want in this format 2010-05-30 15:33:25. If you want only the date, use the date type only.

Answer 2

You can use cast('05/30/2010' as smalldatetime).

If you need to have exact 15:33:25 time then you can use several dateadd calls, e.g. select dateadd(hh, 15, cast('05/30/2010' as smalldatetime)) returns 2010-05-30 15:00:00.

Answer 3

SET DATEFORMAT DMY 
SELECT CAST('30/05/2010' as smalldatetime)

Where do you want the time aspect to come from? The convert above will append 00:00 (midnight) for smalldatetime because:

• the string has no time information
• smalldatetime resolves to a minute resolution

Answer 4

use

select convert(smalldatetime,'30/05/2010',103)