I have a string 010910 in ddMMyy format thatI have to cast this string a SQL Server datetime datatype, like 2010-09-01 00:00:00.000.
How can this be done?
-
3Google "sql server cast string to datetime". The first hit is the answer you need.Pondlife– Pondlife2011-07-22 07:27:05 +00:00Commented Jul 22, 2011 at 7:27
-
1@Pondlife - I don't think there is an option answer for the format I'm working with.Soham Dasgupta– Soham Dasgupta2011-07-22 13:08:39 +00:00Commented Jul 22, 2011 at 13:08
-
That's correct, but hopefully it's obvious that if you know the supported formats, the only thing you need to do is convert your own format to a supported one.Pondlife– Pondlife2011-07-22 13:39:24 +00:00Commented Jul 22, 2011 at 13:39
Add a comment
|
3 Answers
How about something like
DECLARE @String VARCHAR(6)
SELECT @String = '010910'
SELECT CONVERT(DATETIME,LEFT(@String,2) + '/' + SUBSTRING(@String, 3, 2) + '/' + RIGHT(@String,2),3)
Have a look at SQL Server Date Formats and CAST and CONVERT (Transact-SQL)
1 Comment
Soham Dasgupta
Your's works perfectly but I think t-clausen.dk's answer is more precise and short.
SELECT CAST(right(s, 2) + left(s,4) as datetime)
FROM (SELECT '010910' s) a
Comments
Pull the bits out using SUBSTRING and put them back together by concatenating.
