March 2, 2006 at 6:44 am
Guys,
I need help on the following please.
i have stored the information 'birthdate' on the table candidates,as varchar. the birthdate data is loaded to the table candidates through a stored procedure that has
set dateformat dmy (to make them european)
and then
convert (varchar, birthdate, 103).
So when i query the table for birthdate i get the format of the type '18/01/1976'. so far so good.
when however i try to convert this to smalldatetime i get the error "The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value."
How Can i convert to smalldatetime? plz?
March 2, 2006 at 6:57 am
Why the heck do you store the birthdate as varchar???
_/_/_/ paramind _/_/_/
March 2, 2006 at 7:07 am
March 2, 2006 at 7:45 am
Can you show us your script?
One thing to note: This "set dateformat dmy (to make them european)" is NOT a true statement.
SET DATEFORMAT does not make the date any format. It just tells SQL Server how to interpret the date format.
This "convert (varchar, birthdate, 103)" is what makes it European format.
SQL Server doesn't understand how to read this 1/2/2006. As far as SQL Server knows, that could be Jan 2, 2006 or 1 Feb 2006. That is where SET DATEFORMAT comes in.
Your script to convert the date to SMALLDATETIME probably does not include the SET DATEFORMAT. You need to add that.
SET DATEFORMAT DMY
SELECT CONVERT(SMALLDATETIME, birthdate)
FROM urtablename
-SQLBill
March 2, 2006 at 8:01 am
Ok Bill, plz help me with this...
i have a table named candidates. This table has a field called [birthdate] that is of type varchar(50). ok? this field has dates inside (obviously converted to varchars) e.g '18/01/1976'
when i write the follwing in Q.A
SELECT
CONVERT (DATETIME, CONVERT (VARCHAR, BIRTHDATE,103),103)
FROM CANDIDATES
, i get the result ok, in datetime format. but when i write to Q.A
SELECT
CONVERT (SMALLDATETIME, CONVERT (DATETIME, CONVERT (VARCHAR, BIRTHDATE,103),103))
FROM CANDIDATES
i get the message "The conversion from datetime data type to smalldatetime data type resulted in a smalldatetime overflow error".
Can you help plz? cheers.
March 2, 2006 at 9:35 am
hey!!
it is working fine man.
select convert(smalldatetime,'18/01/1976',103)
BUT DATE EARLIER THAN 1900 WILL CAUSE A PROBLEM.!!
Regards
AMIT GUPTA
March 2, 2006 at 10:53 am
Dionisis,
The problem is that you are converting a 'full' DATETIME to SMALLDATETIME.
Try this:
SELECT
CONVERT (SMALLDATETIME, CONVERT (VARCHAR, BIRTHDATE,103),103))
FROM CANDIDATES
There's no need to convert to DATETIME and then to SMALLDATETIME. It's just an extra unnecessary step.
BTW-it's always good practice to provide the length for VARCHAR (ie. VARCHAR(20)). For everything but CAST or CONVERT, the default for VARCHAR is 1.
-SQLBill
December 19, 2008 at 3:41 am
Amit is correct. If you put in the date before 1900, it will bring up that error. However, if you put 01-01-1900, it will let you do it.
Weird!!
September 27, 2011 at 2:11 am
it is perfect and working well
thanks
June 25, 2018 at 10:23 am
Select *
From TableWithOffendingData
Where Try Cast(yourDateTimeField as smalldatetime) is null
Most likely you have a date that is either too far into the future or too far in the past for smalldatetime to handle.
June 25, 2018 at 10:29 am
gregory.davis2 - Monday, June 25, 2018 10:23 AMSelect *
From TableWithOffendingData
Where Try Cast(yourDateTimeField as smalldatetime) is nullMost likely you have a date this is either too far into the future or too far in the past for smalldatetime to handle.
Welcome to the forums,
You made a rookie mistake and answered a question posted 12 years ago. I admire your desire to help and encourage you to review newer posts and read the discussions (and SQL Server version) before posting a solution.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply