asked on

getdate function in sql 2000

Hi Experts.
I have a table calendar in sql 2000
it has 1 column named "dt" storing date the format of storing a date is

' 2004-02-02 00:00:00 '
now in 1 stored procedure i need to extract data based on todays date and then matching it dt column

when i get todays date using getdate() it is in different format

which is
' Jan 28 2007 10:52AM '
how do i change this format matching to my stored format.

my second question is how i can just retrieve date part from ym dt column ?

Regards
Bilal

Guy Hengel [angelIII / a3]

if your column is of data type datetime, you don't have any problems:

select * from yourtable where yourfield >= getdate()

if the field is not datetime, but varchar (bad idea):

select * from yourtable where convert(datetime,yourfield,120) >= getdate()

bsheikh

ASKER

my datatype for that column is smalldatetime

but somehow it is not matching with getdate() as it is in different format.

regards
bilal

Guy Hengel [angelIII / a3]

don't get confused on what you SEE, that is only a display/formatting issue.
when you compare date/time values/columns, there is no "format" used, only the underlying value

bsheikh

ASKER

problem is solves here is the working query.

DECLARE @FISCAL_YEAR as smallint

select @FISCAL_YEAR=FY from calendar where dt=convert(nvarchar,getdate(),101)
SELECT DISTINCT W FROM CALENDAR WHERE FY=@FISCAL_YEAR AND dt<(getdate())

ASKER CERTIFIED SOLUTION

GranMod

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial