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)

varchar to datetime

Author

Topic

SQLboom
Yak Posting Veteran

63 Posts

Posted - 2004-01-12 : 10:01:39

i have a variable as

declare @varchar varchar(100)
set @varchar = 'convert(datetime,''01-Jan-2000'')'

Now i want to insert this value into another datetime value as ::

declare @date datetime
select @date = @varchar

But this is not being allowed.. . .The best i could think was to use a temp table which seems too much for too little. .
.Can someone suggest a better and simpler solution with SQL Server when a "convert" is already with the varchar variable. .. .

Thanks..

X002548
Not Just a Number

15586 Posts

Posted - 2004-01-12 : 10:05:50

Did you look at what you're setting..

cut and paste this in to QA


declare @varchar varchar(100)
set @varchar = 'convert(datetime,''01-Jan-2000'')'
SELECT @varchar

set @varchar = convert(datetime,'01-Jan-2000')
SELECT @varchar

declare @date datetime
select @date = @varchar
SELECT @date

Brett

8-)

SamC
White Water Yakist

3467 Posts

Posted - 2004-01-12 : 10:06:27

declare @myvar datetime
set @myvar = convert(datetime,'01-Jan-2000')

Too many quotes (see above). Also suggest you not name variables using a datatype.

SQLboom
Yak Posting Veteran

63 Posts

Posted - 2004-01-13 : 04:25:03

the variable @varchar's value is obtained from elsewhere [as a parameter to a procedure out of hands for me] and i need to fetch the date from that varchar variable... . It's the way as is in the example i gave.. . .

aespe
Starting Member

2 Posts

Posted - 2004-01-13 : 05:13:01

try to format the varchar's content as
"yyyy-mm-dd hh:nn:ss" to be converted succesfully
to datetime type, the format above will succeded to be converted no matter what regional setting your system is using

sample conversion should be :
convert(datetime, "2000-01-31") -- for converting 31 jan 2000

SQLboom
Yak Posting Veteran

63 Posts

Posted - 2004-01-13 : 07:01:13

didn't get the catch ... ..the date format could help once the varchar datatype got converted to date dataype!

SamC
White Water Yakist

3467 Posts

Posted - 2004-01-13 : 09:04:00

quote:
Originally posted by SQLboom

the variable @varchar's value is obtained from elsewhere [as a parameter to a procedure out of hands for me] and i need to fetch the date from that varchar variable... . It's the way as is in the example i gave.. . .

Hmmm.. The example you posted shows a command "CONVERT" stored in @varchar. Your note above sounds like @varchar holds a value (that suggests a string date to me').

Which is it?

If @varchar holds a date, it can be converted either explicitly or implicitly

DECLARE @MyDateTime DATETIME

set @MyDateTime = @Varchar -- Implicit

set @MyDateTime = CAST(@Varchar As Datetime) -- Explicit

Sam

SQLboom
Yak Posting Veteran

63 Posts

Posted - 2004-01-14 : 10:24:14

once more...

declare @varchar varchar(100)
set @varchar = 'convert(datetime,''01-Jan-2000'')' --> [I get it like this only. . Plz accept it the way it is ..]
--
declare @date datetime
select @date = @varchar --> Errors:
Server: Msg 241, Level 16, State 1, Line 6
Syntax error converting datetime from character string.

So it is not an implicit Conversion. . .It did look fairly simple and implicit to me first but not anymore.. ....

mr_mist
Grunnio

1870 Posts

Posted - 2004-01-14 : 10:34:47

You wont be able to do that. You've got the whole thing in the string. If you really have got the CONVERT as part of the string you would need to do something like...



declare @varchar varchar(100)
set @varchar = 'convert(datetime,''01-Jan-2000'')'

set @varchar = replace (@varchar, 'convert(datetime,''','')
set @varchar = replace (@varchar, ''')','')
set @varchar = replace (@varchar, '-',' ')

select @varchar

declare @date datetime

select @date = @varchar

select @date

-------
Moo. :)

X002548
Not Just a Number

15586 Posts

Posted - 2004-01-14 : 10:40:52

Please accept this as to the only way I know how to make this work...

And btw, why are you getting passed not only data but conversion commands?


 SET NOCOUNT ON
CREATE TABLE #HoopJumping(col1 datetime)
     DECLARE @varchar varchar(100), @cmd varchar(8000), @date datetime
         SET @varchar = 'convert(datetime,''01-Jan-2000'')' --> [I get it like this only. . Plz accept it the way it is ..]
      SELECT @cmd = 'INSERT INTO #HoopJumping(Col1) SELECT ' + @varchar
        EXEC(@cmd)
      SELECT @Date = Col1 FROM #HoopJumping 
      SELECT @date
  DROP TABLE #HoopJumping
 SET NOCOUNT OFF

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources