how to assign a value into Datetime variable

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

SQL Server 2005 Forums

Transact-SQL (2005)

how to assign a value into Datetime variable

Author

Topic

khufiamalik
Posting Yak Master

120 Posts

Posted - 2009-04-01 : 08:03:02

Hello All,

I have a table which is having a field with datatype varchar.
this filed will contain a date having format dd/MM/yyyy like 18/07/2008.

when I get this date from field and try to assign in a datetime variable, I get the following error.

The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

Can any one help me that how to assign this value to datetime variable.

ayamas
Aged Yak Warrior

552 Posts

Posted - 2009-04-01 : 08:06:58

Is it a SQL datetime variable?

khufiamalik
Posting Yak Master

120 Posts

Posted - 2009-04-01 : 08:08:47

yes it is SQL Datetime variable (in SP) , but the datatype of table field from where I am getting this value is varchar.

ayamas
Aged Yak Warrior

552 Posts

Posted - 2009-04-01 : 08:12:59

Declare @str varchar(40)
Declare @dt datetime
set @str='18/07/2008'
set dateformat dmy
set @dt=@str
select @dt

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-01 : 08:54:42

or

Declare @str varchar(40)
Declare @dt datetime
set @str='18/07/2008'
set @dt=convert(datetime,@str,103)
select @dt

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-01 : 08:55:28

But it is better to express dates in unambigious format YYYYMMDD

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources