declare @ValueDate smalldatetime
set @ValueDate = '21/05/2007'
select @ValueDate
error:
The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value.
Please note, if '05/21/2007' is used then there is no error.
How is it possible to get 'dd/mm/yyyy' as above to work please
Thanks
use the set dateformat dmy before your batch..
set dateformat dmy declare @ValueDate smalldatetime set @ValueDate = '21/05/2007' select @ValueDate
Code Snippet
Another altarnative that you might be able to take advantage of is something like:
declare @ValueDate smalldatetime
set @ValueDate = convert(datetime, '21/05/2007', 103)
select @ValueDate as [@ValueDate]
/*
@ValueDate
-------------------
2007-05-21 00:00:00
*/
Get an error:
declare
@ValueDate smalldatetimeset
@ValueDate = convert(datetime, '05/21/2007', 103)select
@ValueDate as [@ValueDate]The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
returns null
even tried using
set
dateformat dmyno progress.
Any thoughts please
Yes, the format can change.
Does this mean there is no way to solve this
If the format is going to change, then you will need to change the format -- the "103" part -- to correspond to the change. But there are more important deeper issues now at stake. If the format can change without knowing in advance which format you are getting it can mean that you have a date with an ambiguous meaning.
In your example you are given a date that might be "21/05/2007" or "05/21/2007". This is bad enough because we might not be sure which flavor we are getting. Now, let me ask you a question:
If we don't know in advance whether we are going to get one of these two formats, how do we really understand the date "05/12/2007" Is this date May 12 or December 5 Hasn't the meaning of this now become ambiguous
Give a look to the article "Cast and Convert" in books online for a list of the various formats that are available.
arkiboys,
Try to use a code that is not dependent of the language in use, or the setting of dateformat. Use the ISO or ISO8601 (see function CONVERT in BOL) format and SQL Server will interpret that string as a datetime with no problem.
Example:
set language English
go
declare @s varchar(25)
set @s = '20070521'
select cast(@s as smalldatetime)
go
set dateformat dmy
go
declare @s varchar(25)
set @s = '20070521'
select cast(@s as smalldatetime)
go
set dateformat ymd
go
declare @s varchar(25)
set @s = '20070521'
select cast(@s as smalldatetime)
go
set language Spanish
go
declare @s varchar(25)
set @s = '20070521'
select cast(@s as smalldatetime)
go
declare @s varchar(25)
set @s = '2007-05-21T09:28:00'
select cast(@s as smalldatetime)
go
AMB