how to get current date using getdate() or any oth

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

Transact-SQL (2000)

how to get current date using getdate() or any oth

Author

Topic

shorty98
Starting Member

1 Post

Posted - 2002-08-27 : 00:14:46

Hi!

I'm new to T-SQL and trying out my queries using the SQL Query Analyzer.

What I intend to do is to retrieve records based on current date. The field is Trans_R.Tr_Date, of type nvarchar(255) and the data inside the field is formatted as yyyymmdd.

My Q's are:

1. Where should I put the WHERE statement at?

2. Is there any other way to work around this problem without resorting to converting the Trans_R.Tr_Date field to Datetime datatype beforehand?

3. I tried using Getdate function as part of the WHERE statement but nothing comes out. Would this WHERE statement work (or make sense)?

WHERE Trans_R.Tr_date = getdate()

Please advise and thanks in advance.

-------------
START OF CODE
-------------

SELECT
Cards.card_number,Trans_R.Tr_Date,Trans_R.staff_number,
MIN(Trans_R.Tr_In) AS Actual_Time_In,
MAX(Trans_R.Tr_Out) AS Actual_Time_Out,

'Status'= CASE
WHEN MIN(Trans_R.Tr_In) IS NULL THEN 'ABSENT'
WHEN MIN(Trans_R.Tr_In) < 171500 THEN 'ATTEND'
ELSE 'ABSENT' END,

'Late Time' = CASE
WHEN (MIN(Tr_In) - 080000> 0) THEN (MIN(Tr_In) - 080000)
ELSE '' END,

FROM Trans_R INNER JOIN Cards
ON Trans_R.Staff_Number = Cards.Staff_Number
GROUP BY Cards.card_number,trans_r.staff_Number,Trans_R.Tr_Date

-------------
END OF CODE
-------------

Sitka
Aged Yak Warrior

571 Posts

Posted - 2002-08-27 : 10:49:39

1. ?

2. and 3.

|
|
V

CAST(CONVERT(char(8),Trans_R.Tr_Date,112) as datetime) = CAST(CONVERT(char(8),GETDATE(),112) as datetime)

Voted best SQL forum nickname...."Tutorial-D"

Edited by - sitka on 08/27/2002 10:57:48

nr
SQLTeam MVY

12543 Posts

Posted - 2002-08-27 : 11:07:22

1. between the on statement and the group by statement.

don't know what Tr_In is but subtracting 80000 from it doesn't look right.
how about - convert(datetime, '08:00:00')
unless it's really held as an integer.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources