Casting - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Casting

Author

Topic

Wouter Benoit
Starting Member

23 Posts

Posted - 2007-07-03 : 08:11:48

Hello,

I'm new to SQL Server and I have to finish a VB.Net program from an employee that has left the company.

I have a table with column 'vanafdatum' witch has datatype char but they stored only dates in this column ( like this: 13/09/2006).

When I try to run the next querie I receive an error message "The conversion of a char data type to a datetime data type resulted in an out of range datetime value".

This is the querie I try to run

SELECT MAX(CAST(vanafdatum AS datetime)) AS vanaf_datum
FROM tarieven
WHERE (vanafdatum <
(SELECT getdate()))

Is there any way I can cast the char datatype to a datetime datatype without getting this error.
I allready tried to change the data type from char to datetime, but doesn't work either.
A possible solution is to erase the column an manually fill in all the fields again in the data type datetime, but that seems a little stupid to do.
So I wonder if there is a solution that can change the data type for all rows in the database without losing any data.

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-07-03 : 08:15:34

i think the error is produced because of where clause...

put the same cast in where clause also

--------------------------------------------------
S.Ahamed

Wouter Benoit
Starting Member

23 Posts

Posted - 2007-07-03 : 08:24:57

That produces the same error.

SELECT MAX(CAST(vanafdatum AS datetime)) AS vanaf_datum
FROM tarieven
WHERE (CAST(vanafdatum AS datetime) <
(SELECT getdate() AS huidige_datum))

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-07-03 : 08:35:23

Find out the values which are causing problem for conversion using ISDate() function:

select vanafdatum
From tarieven
Where IsDate(vanafdatum) = 0

Also, what is the setting of SET DATEFORMAT ?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-03 : 09:39:23

set dateformat dmy

Peter Larsson
Helsingborg, Sweden

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-07-04 : 00:57:01

[code]1 Always use proper datatype DATETIME to store dates
2 Select columns from table where dbo.proper_date(replace(col,'/',''))<Getdate()
--Use Proper_date function at http://sqlteam.com/forums/topic.asp?TOPIC_ID=82164[/code]

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources