Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
Saturday, June 13, 2009 5:56 PM
Hello,
In my select statment, i am selecting the date which is saved as U.S as convert (char(10),datefeildname,103) where i want o convert it to dd/mm/yyyy format. I am refelecting the selected calues into a grid where the user can fill the date. when i am trying to save the date feild i am getting the below error
"
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
The statement has been terminated.
"
so how can i save the datetime value?
thanks
Hiba
Sunday, June 14, 2009 8:47 PM ✅Answered
You do not need to convert DD/MM/YYYY to MM/DD/YYYY. The DATETIME data
type does not have format.
Since your database column is DATETIME, then you simply need to convert
the user entered string to DATETIME, and this is what I gave you
initially. Here is how your insert statement will look like:
INSERT INTO PERS_EP_NSSFDependents (empid, birthdate)
VALUES ('EMP217', CONVERT(DATETIME, '20/06/2009', 103))
--
Plamen Ratchev
http://www.SQLStudio.com
Sunday, June 14, 2009 1:25 AM
It is best to pass the date as date data type parameter. That way you do
not have to do any conversion. If for any reason you have to pass it as
string in the format DD/MM/YYYY, then you have to use the reverse
conversion with CONVERT to get it back to DATETIME data type before
inserting into the table:
DECLARE @date_as_string CHAR(10);
SET @date_as_string = '23/05/2009';
SELECT CONVERT(DATETIME, @date_as_string, 103);
--
Plamen Ratchev
http://www.SQLStudio.com
Sunday, June 14, 2009 1:37 AM
It's assuming that you're providing dates in MM/DD/YYYY format. You can easily convert your date to YYYYMMDD format to eliminate any ambiguity. Have look at this:
--Not OK
SELECT CAST('30/01/2009' AS datetime)
--Dandy
SELECT CAST('01/30/2009' AS datetime)
--Use this logic
DECLARE @MyDateString varchar(10)
SET @MyDateString = '30/01/2009'
SELECT CAST(
RIGHT(@MyDateString, 4) +
SUBSTRING(@MyDateString, 4, 2) +
LEFT(@MyDateString, 2)
AS datetime)
Aaron Alton | thehobt.blogspot.com
Sunday, June 14, 2009 4:04 AM
Hiba,
You should always store dates in their native data type, when possible because you can avoid regional setting mismatches, invalid conversions etc.. The problem you are experiencing stem from the regional settings, on your SQL Server Instance. By default the US uses the regional date format is MM/DD/YYYY. If you try to cast or convert a character column into a date, with the format DD/MM//YYYY, you may get an out of scope error because the value may not be valid. E.g. 13-06-2009 would be converted as month 13 day 6 year 2009, which is not valid, as there are only 12 months; however, a value of 01-02-2009 will be valid, but completely incorrect.
This is how you can recreate the problem:
DECLARE @dt CHAR(10)
SET @dt = '13/06/2009'
--This fails
SELECT CONVERT(CHAR(10),CONVERT(DATETIME,@dt),101);
GO
You can use the date format set option to override the regional settings, but this is more of a bandaid than a solution. Here is a sample:
DECLARE @dt CHAR(10)
SET @dt = '13/06/2009'
SET DATEFORMAT DMY;
SELECT CONVERT(CHAR(10),CONVERT(DATETIME,@dt),101);
GO
The best solution to this problem is to store the data as a datetime or smalldatetime; however, the next best solution is to convert the character value, using the convert format specification 103. A lot of developers forget that you can format a date when you are converting from string to date and from date to string. In most cases, a developer goes from datetime to character because of the native data type benefits mentioned before.
Here is the solution to your problem. Essentially we are converting the character value into a date, but specifiing the format you are using to store the variable.
--to resolve the issue
DECLARE @dt CHAR(10)
SET @dt = '13/06/2009'
SELECT CONVERT(CHAR(10),CONVERT(DATETIME,@dt,103),101);
GO
Sunday, June 14, 2009 6:10 PM
Hello,
How can i convert the date such as 14/06/2009 in the Insert statment into 06/14/2009 in order not to encounter an error?
Thanks
Hiba
Sunday, June 14, 2009 6:13 PM
Hello,
How can i convert the date such as 14/06/2009 in the Insert statment into 06/14/2009 in order not to encounter an error?
ThanksHiba
Hiba,
You need only use the code that we provided and switch out your column names.
insert into Mytable (<MyDateColumn>)
SELECT CONVERT(CHAR(10),CONVERT(DATETIME,<MyDateColumn>,103),101)
from <MyTable>
GO
Sunday, June 14, 2009 6:19 PM
Hello,
If i have the following insert statment:
insert
into PERS_EP_NSSFDependents (empid,birthdate) values ('EMP217','20/06/2009')
then how can I modify it to work the way u said cuz i didnt get it exactly.
Thanks
Hiba
Sunday, June 14, 2009 6:31 PM
Hiba,
I am a little confused, are you inserting a hard coded value or a parameter/column value? If you are using a hard-coded value you can just change the dates aroud. E.g.
INSERT INTOPERS_EP_NSSFDependents (empid,birthdate) VALUES ('EMP217','06/20/2009')
If you are using parameter/column values you will need to do this:
INSERT INTOPERS_EP_NSSFDependents (empid,birthdate) VALUES ('EMP217',CONVERT(CHAR(10),CONVERT(DATETIME,<MyDateColumn>/@ParameterValue,103),101))
Here is a fully functional sample:
--to resolve the issue
DECLARE @dt CHAR(10)
SET @dt = '20/06/2009'
DECLARE @t TABLE(
empid CHAR(10),
dt CHAR(10)
)
INSERT INTO @t (empid,dt)
VALUES ('EMP217',CONVERT(CHAR(10),CONVERT(DATETIME,@dt,103),101))
SELECT *
FROM @t
Sunday, June 14, 2009 6:41 PM
Hello,
I am using the hard coded one,in your sql statment, you've already wrote in ur sql stmt a date in the US format, but in my case i am getting the value from the user as '20/06/2008' and not '06/20/2008'.
Thanks
Hiba
Sunday, June 14, 2009 6:45 PM
Hiba,
I dont know how else to tell you that if you are getting the value from the user, it is probably a parameter, in which case you should use the second example I gave, as this works with parameter values.
Edit: here is the sampel I am talking about to avoid anymore confusion.
--to resolve the issue
DECLARE @dt CHAR(10)
SET @dt = '20/06/2009'
DECLARE @t TABLE(
empid CHAR(10),
dt CHAR(10)
)
INSERT INTO @t (empid,dt)
VALUES ('EMP217',CONVERT(CHAR(10),CONVERT(DATETIME,@dt,103),101))
SELECT *
FROM @t
-Adam