Sql Date Time - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Sql Date Time

Author

Topic

shulink
Starting Member

12 Posts

Posted - 2008-02-29 : 17:02:26

I have date time function that calculates the birthday base on the age that supply. It used to work fine, but all of a sudden, it breaks today, so I think it has some wrong formatting.

SELECT @StartBirthday = CAST(CONVERT(varchar, YEAR(GETDATE())-@StartAge) + '-' + CONVERT(varchar, MONTH(GETDATE())) + '-' + CONVERT(varchar, DAY(GETDATE())) as smalldatetime)

The error message is:
System.Data.SqlClient.SqlException: The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value.

Does anyone know how to fix this? Thanks a lot.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-02-29 : 17:43:13

Today is Feburary 29th. The year you are concatinating probably is not a leap year, hence the conversion failure.

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-02-29 : 21:13:06

So they past an age, and you are calculating their actual date of birth?

why are doing all that converting and casting..


Declare @age int
Declare @today datetime
Declare @Start datetime
Set @today = dateadd(d,0,datediff(d,0,getdate()))

SET @age = 33

SELECT @Start = dateadd(yy,-@age,@today)

Print @start

converting date parts to strings and casting it as small date time, only to get "today's date the {age} number of years ago" seems awfully painful way to do things.

Poor planning on your part does not constitute an emergency on my part.

shulink
Starting Member

12 Posts

Posted - 2008-02-29 : 23:21:23

Hi, thanks for all the help. It works perfectly.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources