Conversion of varchar into Datetime

Question

Why does the following code work:

CAST(DateOfAction AS Date)

but this code does not:

CAST(DateOfAction AS Datetime)

note: DateOfAction is a varchar field

Can someone explain this and give the right code?

Can you give an example of the contents of `DateOfAction`? Also I assume SS 2008? — Martin Smith, Jan 13 '11 at 16:26
example of DateOfAction are '20091102', '20100228'.. and these are stored as varchar. and some of the data are 'NULL' value. the error message i am getting: "Msg 242, Level 16, State 3, Line 1 The conversion of a varchar data type to a datetime data type resulted in an out-of-range value. Warning: Null value is eliminated by an aggregate or other SET operation." many thanks. — Shariful, Jan 14 '11 at 12:06

score 0 · Answer 1 · answered Jan 13 '11 at 16:29

Possibly because your VARCHAR field contains a value that might convert to a DATE (no time values), but isn't in any of the valid supported formats that SQL Server CAST and CONVERT support.

Since you didn't provide any samples of what your data looks like, we can only guess.....

Check the SQL Server Books Online on CAST and CONVERT for all supported, valid formats that will convert to DATETIME.

score 0 · Answer 2 · edited Jun 20 '20 at 09:12

0

Edit

Following the additional information that you are using yyyymmdd format I can only think the problem is in the data. Can you try the following?

Is every value in the format you think it is?

 SELECT DateOfAction 
 FROM ResAdm.Action 
 WHERE DateOfAction NOT LIKE '[1-2][0-9][0-9][0-9][0-1][0-9][0-3][0-9]'

Is every value in the range acceptable to datetime?

SELECT DateOfAction 
FROM ResAdm.Action 
WHERE cast(DateOfAction as DATE) < '17530101'

edited Jun 20 '20 at 09:12

Community

1
1

answered Jan 13 '11 at 16:29

Martin Smith

438,706
87
741
845

this code work: SELECT P.RecClassID, B.BranchName, P.PersonID, (SELECT MIN(CAST(A.DateOfAction AS Date)) AS Expr1 FROM ResAdm.Action A WHERE (A.PersonID = P.PersonID)) AS PeriodFrom, (SELECT MAX(CAST(A.DateOfAction AS Date)) AS Expr1 FROM ResAdm.Action A WHERE (A.PersonID = P.PersonID)) AS PeriodTo FROM ResAdm.Person P INNER JOIN ResAdm.Action A ON P.PersonID = A.PersonID INNER JOIN ResAdm.Branch_Codes B ON P.BranchID = B.BranchID – Shariful Jan 14 '11 at 12:13
Hi Martin, i found 30 records when run the 1st sql statement and 31 for the 2nd one. What should i do next?? – Shariful Jan 14 '11 at 14:12
Well in that case it looks like you need to fix your data then as it looks like you have 31 dates earlier than that in it. – Martin Smith Jan 14 '11 at 15:42
1

I have fixed the data in our database and rightly so the problem is solved now. Thank you sooooo much Martin for your kind help. Wishing you all the best for your programming future. -- Sharif – Shariful Jan 14 '11 at 16:07

score 0 · Answer 3 · edited May 23 '17 at 11:55

0

Date has "fixed" behaviour for some values that caused issues with datetime.

For example yyyy-mm-dd is not language safe even though ISO

See this article by Tibor Karaszi. And this SO question (in the comments)

edited May 23 '17 at 11:55

Community

1
1

answered Jan 14 '11 at 05:24

gbn

422,506
82
585
676

score 0 · Answer 4 · answered Jan 14 '11 at 13:19

Besides the other answers pointing out that date has certain conversions that are safe that aren't for datetime, there's also the fact that the range of date is larger than that for datetime. So if you have dates earlier than 1753, no amount of formatting/conversion will work.

Also, if this isn't for converting code to work on an older server, but just to gain a time component, then if you've got date available on your server, you should have datetime2, which will work a lot better.

Conversion of varchar into Datetime

4 Answers4

Edit