Current Date - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Current Date

Author

Topic

snowfake
Starting Member

3 Posts

Posted - 2007-12-20 : 08:36:38

Does anyone know how I can get the current date inserted to a SQL query to only get results of today?

My SQL is:
SELECT Business.BusinessCODE, Business.Name, Business.BusinessNumber, Business.BusinessStatusCODE, Business.Done, Business.StartDATE, Business.EndDATE, Business.TotalPrice, Business.Contribution, Business.BusinessProcessCODE, Customer.Name1, CaesarUser.FirstName, CaesarUser.Surname, BusinessHistory.UpdateDATE
FROM Movex_SMS_50_Server.dbo.Business Business, Movex_SMS_50_Server.dbo.BusinessHistory BusinessHistory, Movex_SMS_50_Server.dbo.CaesarUser CaesarUser, Movex_SMS_50_Server.dbo.Customer Customer, Movex_SMS_50_Server.dbo.relCaesarUserBusiness relCaesarUserBusiness
WHERE Business.CustomerID = Customer.CustomerID AND relCaesarUserBusiness.BusinessID = Business.BusinessID AND BusinessHistory.BusinessID = Business.BusinessID AND relCaesarUserBusiness.CaesarUserID = CaesarUser.CaesarUserID AND
((BusinessHistory.Description='Créé' OR
BusinessHistory.Description='Created' OR
BusinessHistory.Description='Creado' OR
BusinessHistory.Description='Erstellt' OR
BusinessHistory.Description='Creato')AND
((BusinessHistory.updatedate = TODAYS DATE)) AND
(Customer.EmployeeCountCODE=0xEC6D3BDB8B6FD511AC240006294308D9 OR
Customer.EmployeeCountCODE=0xFC673BDB8B6FD511AC240006294308D9))
ORDER BY CaesarUser.Surname, Customer.Name1

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-20 : 08:38:17

AND BusinessHistory.UpdateDate >= DATEADD(DAY, DATEDIFF(DAY, '19000101', CURRENT_TIMESTAMP), '19000101')
AND BusinessHistory.UpdateDate < DATEADD(DAY, DATEDIFF(DAY, '19000101', CURRENT_TIMESTAMP), '19000102')

E 12°55'05.25"
N 56°04'39.16"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2007-12-20 : 08:38:24

use GETDATE()
If time is not required strip it off as

DATEADD(d,DATEDIFF(d,0,GETDATE()),0)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-12-20 : 08:38:49

getdate() will return current date & time.

if you only need the date with time at midnight
dateadd(day, datediff(day, 0, getdate()), 0)

does your BusinessHisotry.updatedate contains time ?

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-12-20 : 08:39:11

KH
[spoiler]Time is always against us[/spoiler]

snowfake
Starting Member

3 Posts

Posted - 2007-12-20 : 09:06:42

Thanks very much for your answers, however I do not seem to get it working with your queries. I keep on getting 0 results whilst I am sure I should have at least 50 results. The businesshistory.updatedate indeed contains the date and time as follows: 20-12-2007 13:25

My collegue came up with the following, but also this has 0 results:
SELECT Business.BusinessCODE, Business.Name, Business.BusinessNumber, Business.BusinessStatusCODE, Business.Done, Business.StartDATE, Business.EndDATE, Business.TotalPrice, Business.Contribution, Business.BusinessProcessCODE, Customer.Name1, CaesarUser.FirstName, CaesarUser.Surname, BusinessHistory.UpdateDATE
FROM Movex_SMS_50_Server.dbo.Business Business, Movex_SMS_50_Server.dbo.BusinessHistory BusinessHistory, Movex_SMS_50_Server.dbo.CaesarUser CaesarUser, Movex_SMS_50_Server.dbo.Customer Customer, Movex_SMS_50_Server.dbo.relCaesarUserBusiness relCaesarUserBusiness
WHERE Business.CustomerID = Customer.CustomerID AND relCaesarUserBusiness.BusinessID = Business.BusinessID AND BusinessHistory.BusinessID = Business.BusinessID AND relCaesarUserBusiness.CaesarUserID = CaesarUser.CaesarUserID AND
((BusinessHistory.Description='Créé' OR
BusinessHistory.Description='Created' OR
BusinessHistory.Description='Creado' OR
BusinessHistory.Description='Erstellt' OR
BusinessHistory.Description='Creato') AND
(cast((BusinessHistory.updatedate-2) as datetime)=
(select convert(varchar,getdate(),102))) AND
(Customer.EmployeeCountCODE=0xEC6D3BDB8B6FD511AC240006294308D9 OR
Customer.EmployeeCountCODE=0xFC673BDB8B6FD511AC240006294308D9))
ORDER BY CaesarUser.Surname, Customer.Name1

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-20 : 09:10:26

What datatype are you using for column BusinessHistory.UpdateDate?

E 12°55'05.25"
N 56°04'39.16"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2007-12-20 : 09:11:49

Make sure updatedate and GETDATE() both has no time part. Strip time part from update date as well.

snowfake
Starting Member

3 Posts

Posted - 2007-12-20 : 09:43:25

We are using SQL server 2000 and the date type is for example:
39436,509178240703 (this is todays date + time).

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-20 : 10:02:14

AND BusinessHistory.UpdateDate >= DATEDIFF(DAY, '19000101', CURRENT_TIMESTAMP)
AND BusinessHistory.UpdateDate < DATEDIFF(DAY, '18991231', CURRENT_TIMESTAMP)

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-20 : 10:09:44

quote:
Originally posted by visakh16

Make sure updatedate and GETDATE() both has no time part. Strip time part from update date as well.

Why UpdateDate? If you do this, then no present INDEX can save your ass.

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources