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


bsheikhAsked:
Who is Participating?
 
GranModCommented:
Closed, 75 points refunded.
GranMod
The Experts Exchange
Community Support Moderator of all Ages
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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()
0
 
bsheikhAuthor Commented:
my datatype for that column is smalldatetime

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

regards
bilal
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
0
 
bsheikhAuthor Commented:
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())

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.