converting datetime from character string

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 2000 Forums

SQL Server Development (2000)

converting datetime from character string

Author

Topic

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2008-12-11 : 14:40:49

I am getting error while executing the below script.

Select DT_YR,DT_MNTH,DT_DT from table where
CAST(CAST(DT_YR AS VARCHAR) + RIGHT('0' + CAST(DT_MNTH AS VARCHAR), 2) + RIGHT('0' + CAST(DT_DT AS VARCHAR), 2) AS
DATETIME) Between '1/1/2008' And '2/2/2008'

Columns are int type..

Error: Syntax error converting datetime from character string

Thanks for your help in advance.

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2008-12-12 : 00:27:37

try this

Select DT_YR,DT_MNTH,DT_DT from 
table where
CAST(RIGHT('0' + CAST(DT_MNTH AS VARCHAR(10)), 2) +'/'
	+ RIGHT('0' + CAST(DT_DT AS VARCHAR(10)), 2) + '/'
	+ CAST(DT_YR AS VARCHAR(10)) AS DATETIME) 
	Between '1/1/2008' And '2/2/2008'

"There is only one difference between a dream and an aim.
A dream requires soundless sleep to see,
whereas an aim requires sleepless efforts to achieve..!!"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-12-12 : 01:51:24

declare @day int, @month int, @year int
select @day=12,@month=3,@year=2008
select dateadd(day,@day-1,dateadd(month,@month-1,dateadd(year,@year-1900,0)))

Use the above logic

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-12 : 01:55:04

Somewhat simpler

SELECT	DATEADD(MONTH, 12 * @Year - 22801 + @Month, @Day - 1)

E 12°55'05.63"
N 56°04'39.26"

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2008-12-12 : 10:18:55

Thanks a lot!!

X002548
Not Just a Number

15586 Posts

Posted - 2008-12-12 : 10:34:42

That's also assuming that all the dateparts are in a valid range.

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources