DateTime Error

Question

Post reply

DateTime Error

mandirkumar

SSC-Addicted

Points: 445
More actions
December 28, 2011 at 8:12 am

#265457

declare @tbl table(stdate datetime)
insert @tbl select '12-31-2011'
declare @date datetime
set @date = '2011-31-12'
select * from @tbl where stdate = @date
Giving the following error
Msg 242, Level 16, State 3, Line 6
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
So I need to convert the variable format to sql format. Please help me out of this.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Arthur Kirchner Ten Centuries Points: 1239 More actions · Answer 1

Maybe always set the Dateformat explicitly:

SET DATEFORMAT MDY

declare @tbl table(stdate datetime)

insert @tbl select '12-31-2011'

SET DATEFORMAT YDM

declare @date datetime

set @date = '2011-31-12'

select * from @tbl where stdate = @date

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 2

Or use the universal format yyyymmdd:

SET @date = '20111231'

This will work no matter what your local settings are.

John

mandirkumar SSC-Addicted Points: 445 More actions · Answer 3

The solution u gave will work fine, but how to know the current date format in sql, so that i can give that dateformat as

SET DATEFORMAT Current Date Format In Sql

kingkong0924 SSC Enthusiast Points: 138 More actions · Answer 4

http://www.sql-server-helper.com/tips/date-formats.aspx

This link may help u

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 5

mandirkumar (12/29/2011)
The solution u gave will work fine, but how to know the current date format in sql, so that i can give that dateformat as
SET DATEFORMAT Current Date Format In Sql

You don't need to know it. The command will override the default behavior for the session.

SET DATEFORMAT YDM --'2011-31-12'

SET DATEFORMAT (Transact-SQL)

http://msdn.microsoft.com/en-us/library/ms189491.aspx