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
2
-
1Any chance you can convert it client-side first? Typically most programming languages and runtimes have far better ways to do this than the database engine.Lasse V. Karlsen– Lasse V. Karlsen2010-05-30 12:50:38 +00:00Commented May 30, 2010 at 12:50
-
I was hoping to avoid this solution...Itay.B– Itay.B2010-05-30 12:53:14 +00:00Commented May 30, 2010 at 12:53
Add a comment
|
4 Answers
use
select convert(smalldatetime,'30/05/2010',103)
1 Comment
Sean Reilly
This page lists all of the date/time formats supported by the convert function: msdn.microsoft.com/en-us/library/ms187928.aspx
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
Comments
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.
Comments
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.
2 Comments
Itay.B
But my string is 30/05/2010 and not 05/30/2010. cast didn't work on my string.
Andrew Bezzub
Thats because of different culture settings.