converting Datatypes:

Hi All,

how do you convert from a date to an int ? as well as converting from Varchar to and Int

in SQL server 2000 ?

I am retrieving the GetDate() which i store in column as Varchar, i then want to use it within my select statement to calulate data which i want to return i.e

DECLARE @.DateValue AS VARCHAR(20)

SELECT @.DateValue = ApplicationSettingValue FROM ApplicationSettings WHERE ApplicationSettingKey = 'ProcessDate'

print convert(int,@.DateValue) - 5
print GetDate() - 35

SELECT ccy_code, NULL, xrate_date, sterling_xrate
FROM SylvanTrans.dbo.SIADHP_XRate_Hist
WHERE xrate_date >= (CONVERT(int, @.DateValue) - 35 )
ORDER BY xrate_date

now my as you can see in my WHERE CLAUSE i want to calculate what is returned using the GETDATE() stored in my @.DateValue Variable, but the conversion throws a syntax error:

Server: Msg 245, Level 16, State 1, Line 3
Syntax error converting the varchar value 'GetDate()' to a column of data type int.

what am idoing wrong? i know it is something simple, unless there is no conversion from varchar to int?, i also tried setting the datatype for my variable as datetime but i got the same sort of error with DATETIME replacing the VARCHAR in the error!!!

thanks

regards

Hi,

why don′t use use the associated function for dealing with datetime like DATEADD ? You can also add negative dateparts to a datetime like
DATEADD(dd,-35,SomeDate)

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

thanks for your reply,

yes i did see that function, but i neet to have a seperate table which stores the GetDate() so that in my sotred procs

i can do a select on its location and do things like :

select * from tablename

where @.valueDate - 1 > 5

where @.valueDate has the getDate () from my Applicationsettings Table, if i use the DateAdd() i wont be able to do this you see...

|||I think I did not got your point. COuld you explain this in more detail ?

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de