Convert varchar to datetime - 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

SQL Server 2000 Forums

SQL Server Development (2000)

Convert varchar to datetime

Author

Topic

mcordewener
Starting Member

19 Posts

Posted - 2005-11-08 : 05:29:17

Hi there,

I am trying to convert a varchar to datetime. To do this I created a Stored procedure and a function.

Stored procedure:
CREATE PROCEDURE [dbo].[Recente_datum]
AS
UPDATE of_mp1 SET of_mp1.Recente_datum = dbo.New_date ([of_mp1].[rb] , Getdate())
FROM of_mp3 INNER JOIN of_mp1 ON of_mp3.Contract=of_mp1.Contract inner join dbo.Select_voor_nieuwe_opname_datum() as c on of_mp1.Contract= c.contract
WHERE ( dbo.of_mp1.uith_datum = CONVERT(DATETIME, '9999-12-31', 102))
GO

Function:
CREATE FUNCTION [dbo].[New_date] (@RB char, @today datetime)
Returns datetime
BEGIN
Declare @year as varchar
Declare @month as varchar(2)
Declare @day as varchar(2)
Declare @New_date as datetime
Declare @temp as varchar(8)

Set @year= datepart (yyyy, @today)
Set @month = @RB
Set @day = '01'
Set @temp = @year+@month+@day
Set @New_day = DATEADD(MM, -2, convert(datetime, @temp))

Return @new_date
END

The column in the table is defined as a Datetime.
The value of @RB is between 01 and 12

When I run de storedprocedure I recieve the error can not convert varchar to datetime.

What do I do wrong? Who can help me?

greetz,

Marcel Cordewener

Frank Kalis
Constraint Violating Yak Guru

413 Posts

Posted - 2005-11-08 : 06:03:42

@RB char is equivalent to @RB CHAR(1).
Declare @year as varchar is equivalent to Declare @year as varchar(1)

You should use the appropriate variable widths.

--
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt? http://www.insidesql.de/blogs
Ich unterstütze PASS Deutschland e.V. (http://www.sqlpass.de)

Kristen
Test

22859 Posts

Posted - 2005-11-08 : 06:29:26

I wish SQL Server would throw a warning when this syntax is used - its an accident waiting to happen!

Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-11-08 : 06:39:52

Without function, try this

Replace

SET of_mp1.Recente_datum = dbo.New_date ([of_mp1].[rb] , Getdate())

by

SET of_mp1.Recente_datum = Convert(DateTime,Left(year(Getdate())+[of_mp1].[rb]+'01',8))

Madhivanan

Failing to plan is Planning to fail

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-11-08 : 08:35:18

Maybe because the year 9999 might not be valid?

>> CONVERT(DATETIME, '9999-12-31', 102))

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-11-08 : 08:51:39

quote:
Originally posted by jsmith8858

Maybe because the year 9999 might not be valid?

>> CONVERT(DATETIME, '9999-12-31', 102))

Seems valid

Declare @t table(d datetime)
Insert into @t 
Select getdate() union all 
Select getdate()+1000 union all 
Select getdate()+10000 union all 
Select getdate()+200 union all 
Select '9999-12-31'

select d from @t

Select d from @t where  datediff(day,d,CONVERT(DATETIME, '9999-12-31',102))>0

Madhivanan

Failing to plan is Planning to fail

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2005-11-08 : 09:23:23

Why not use this instead:

CREATE FUNCTION [dbo].[New_date] (@RB varchar(2), @today datetime) 
Returns datetime
--Returns The first day of the month two months prior to a submitted month for the current year.
BEGIN
Return dateadd(month, Convert(int, @RB)-3, dateadd(year, datediff(year, 0, @today), 0))
END

mcordewener
Starting Member

19 Posts

Posted - 2005-11-09 : 03:59:12

Thnx,

it works fine now..
I used the none function method:

SET of_mp1.Recente_datum = Convert(DateTime,Left(year(Getdate())+[of_mp1].[rb]+'01',8))

Greetz,

Marcel

Kristen
Test

22859 Posts

Posted - 2005-11-09 : 05:40:39

"year(Getdate())"

I would have expected this to need an explicit cast?

CONVERT(varchar(20), year(Getdate()))

Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-11-09 : 06:12:40

No need as I used Left

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources