Varchar to datetime

Question

Varchar to datetime

compkitty

SSC Rookie

Points: 28
More actions
January 13, 2005 at 11:48 am

#60381

Hi, new to this forum... I have asp.net/vb.net program that uses a Stored Procedure to update a table... However, 4 of the fields are datetime (in the program they are 2(date) 2(time)) I have them in the program as varchar, and then I send them into parameters w/ the SP and need to change them to datetime, but when I do I get Null... Please help
Here is my stored procedure

CREATE PROCEDURE Sp_UpdPTO
(
@FirstName varchar (50),
@Lastname varchar(50),
@DateL varchar (10),
@DateR varchar (8),
@TimeIn varchar (8),
@TimeOut varchar(8),
@Memo varchar (80),
@RSN int,
@LoginId varchar (50)
)
AS
DECLARE @DateLV datetime, @DateRV datetime, @TimeInV datetime, @TimeOutV datetime
SELECT @DateLV=CONVERT(datetime, @DateL), @DateRV=CONVERT(datetime, @DateR), @TimeInV=CONVERT(datetime,@TimeIn), @TimeOutV=CONVERT(datetime, @TimeOut)

UPDATE tblDTPTO
SET DateLeaving = @DateL , ateReturning=@DateR">DateReturning=@DateR, TimeIn = @TimeIn, TimeOut = @TimeOut, Memo=@Memo, DTFname =@FirstName, DTLName = @LastName
WHERE PTORSN = @RSN AND DTLoginId = @LoginId
GO

am I doing this wrong? I'm using Sql Server 2000 t-sql..

THANKS

Viewing 8 posts - 1 through 7 (of 7 total)

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

Charles Otten Default port Points: 1475 More actions · Answer 1

Can you supply some sample data of what you are passing? If the character strings are not in a usuable format you could get a null.

If the phone doesn't ring...It's me.

Frank Kalis SSC Guru Points: 111183 More actions · Answer 2

See, if this helps: http://www.karaszi.com/sqlserver/info_datetime.asp

It would also be helpful, if you could post typical input parameters for the stored procedure.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

compkitty SSC Rookie Points: 28 More actions · Answer 3

here are some of the data that I am passing...

Type Direction Name Value

Varchar in @DateL 2002/05/05

Varchar in @DateR 1-1-2005

Varchar in @timeout 12:00 am

THANKs

Frank Kalis SSC Guru Points: 111183 More actions · Answer 4

Just a quick script

declare @timeout varchar(10)

set @timeout = '12:00 am' --your data

select cast(@timeout as datetime)

set @timeout = '12:00:00' --slightly modified

select cast(@timeout as datetime)

------------------------------------------------------

1900-01-01 00:00:00.000

(1 row(s) affected)

------------------------------------------------------

1900-01-01 12:00:00.000

(1 row(s) affected)

The first statement results in pretty useless data, while the second yields a valid date. I don't know much .Net, but read the link I've posted. Try to pass the parameters in an independent valid format.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

compkitty SSC Rookie Points: 28 More actions · Answer 5

I got it to work; THANKS, now I want to trim off the date so it's just the time...

Frank Kalis SSC Guru Points: 111183 More actions · Answer 6

Forget it! Again, please read the link I've posted. The currently implemented DATETIME datatype *always* contains both, a date and a time portion. But the effects of this and the pitfalls are pretty well explained in Tibor's article.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

compkitty SSC Rookie Points: 28 More actions · Answer 7

I apologize, I saved the document and didn't have a chance to read until **after** I put up that last post... I appreciate the information... I have my parameters pulling it down in the right format; so I will leave it...

again, thanks...