todays 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

todays date

Author

Topic

Rembrantai
Starting Member

1 Post

Posted - 2007-08-20 : 02:23:29

Hi im new to SQL. Ive looked through your forums but most answers seem to complicated.

I'm trying to get todays date for a SELECT statement.

My code is:

List all the records bought today.

SELECT recordNo, recordName
FROM Record r, Properties p
WHERE r.recordNo = p.recordNo AND purchaseDate = '20/08/07';

I'm certain writing '20/08/07' is not right and it should be something like: getDate.

Can anyone help? thankyou

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-08-20 : 04:00:23

[code]SELECT recordNo, recordName
FROM Record r, Properties p
WHERE r.recordNo = p.recordNo AND purchaseDate>=dateadd(day,datediff(day,0,GETDATE()),0)
[/code]
provided purchaseDate is of Datetime datatype

Madhivanan

Failing to plan is Planning to fail

mageshks
Yak Posting Veteran

59 Posts

Posted - 2007-08-21 : 12:12:53

SELECT recordNo, recordName
FROM Record r, Properties p
WHERE r.recordNo = p.recordNo AND purchaseDate = cast ( cast (getdate() as varchar(12)) as datetime)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-08-22 : 01:14:48

quote:
Originally posted by mageshks

SELECT recordNo, recordName
FROM Record r, Properties p
WHERE r.recordNo = p.recordNo AND purchaseDate = cast ( cast (getdate() as varchar(12)) as datetime)

You dont need to double cast. See my reply

Madhivanan

Failing to plan is Planning to fail

mohit_sme
Starting Member

13 Posts

Posted - 2007-08-22 : 13:15:52

Hi

This will also help in standard SQL configuration.

SELECT recordNo, recordName
FROM Record r, Properties p
WHERE r.recordNo = p.recordNo AND purchaseDate = CONVERT(VARCHAR(10), GETDATE(), 121);

Thanks
Mohit Nayyar
http://mohitnayyar.blogspot.com

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-08-22 : 13:23:54

quote:
Originally posted by mohit_sme

Hi

This will also help in standard SQL configuration.

SELECT recordNo, recordName
FROM Record r, Properties p
WHERE r.recordNo = p.recordNo AND purchaseDate = CONVERT(VARCHAR(10), GETDATE(), 121);

Thanks
Mohit Nayyar
http://mohitnayyar.blogspot.com

Mohit

In your code you are comparing a date against a string.
Follow Madhivannan's advise which keeps the right side of the equation still in datetime format.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

mohit_sme
Starting Member

13 Posts

Posted - 2007-08-26 : 00:56:35

Dinakar

Yes, you are right, but as per the first thread, he just passed a date string, so technically he needs something (a date string) instead of passing a fixed string.

Thats why I thought of writing this. And even this is something standard to pass dates.

Thanks
Mohit Nayyar
http://mohitnayyar.blogspot.com

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-08-27 : 01:58:59

quote:
Originally posted by mohit_sme

Hi

This will also help in standard SQL configuration.

SELECT recordNo, recordName
FROM Record r, Properties p
WHERE r.recordNo = p.recordNo AND purchaseDate = CONVERT(VARCHAR(10), GETDATE(), 121);

Thanks
Mohit Nayyar
http://mohitnayyar.blogspot.com

If purchaseDate has both Date and Time, your solution wont work
As I told, you dont need to convert dates to Strings

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources