[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

getdate function in sql 2000

Posted on 2006-03-22
6
Medium Priority
?
638 Views
Last Modified: 2010-08-05
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


0
Comment
Question by:bsheikh
  • 2
  • 2
5 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16258653
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
 

Author Comment

by:bsheikh
ID: 16258711
my datatype for that column is smalldatetime

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

regards
bilal
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16258792
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
 

Author Comment

by:bsheikh
ID: 16258914
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
 

Accepted Solution

by:
GranMod earned 0 total points
ID: 16371008
Closed, 75 points refunded.
GranMod
The Experts Exchange
Community Support Moderator of all Ages
0

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question