Selection on today's 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

SQL Server 2000 Forums

Transact-SQL (2000)

Selection on today's date

Author

Topic

Corobori
Posting Yak Master

105 Posts

Posted - 2002-03-05 : 16:59:42

I would like to build a view where records are shown with a where clause on today's date.

I am new to SQL Server and am struggling with date types.

My field is of datetime format.

In MsAccess I'll do it this way:

SELECT tblNoticias.No_Id
FROM tblNoticias
WHERE (((tblNoticias.No_InputFecha)=Date()));

Jean-Luc

JamesT
Yak Posting Veteran

97 Posts

Posted - 2002-03-05 : 17:03:22

Close, look on Books On Line at GetDate().

yakoo
Constraint Violating Yak Guru

312 Posts

Posted - 2002-03-05 : 17:05:14

The function in SQL is getdate() and is basically Now() as it returns time as well. You might have to convert it to just a date format with either DatePart functions or the convert function. Check BOL for more details.

JamesT
Yak Posting Veteran

97 Posts

Posted - 2002-03-05 : 17:19:53

Something like this would work. You have to do the datepart bit because you have time in the datetime field. Unless you want to match down to the actual time you have to strip out the date specifics:

SELECT tblNoticias.No_Id
FROM tblNoticias
WHERE datepart(day,tblNoticias.No_InputFecha) = Datepart(day, GetDate())
and datepart(month,tblNoticias.No_InputFecha) = Datepart(month, GetDate())
and datepart(year,tblNoticias.No_InputFecha) = Datepart(year, GetDate())

yakoo
Constraint Violating Yak Guru

312 Posts

Posted - 2002-03-05 : 17:36:50


SELECT tblNoticias.No_Id
FROM tblNoticias
WHERE tblNoticias.No_InputFecha = DateAdd(hh, -DatePart(hh, getDate()), DateAdd(mi, -DatePart(mi, getDate()), DateAdd(s, -DatePart(s, getDate()), dateadd(ms, -DatePart(ms, getDate()), getDate()))));

This will subtract miliseconds, then seconds, then minutes, then hours from the current date to just get the date. This is not as clean and readable as James' Post.

or


SELECT tblNoticias.No_Id
FROM tblNoticias
WHERE tblNoticias.No_InputFecha = convert(char(10), getdate(), 101)

convert(char(10), getdate(), 101) will return just the dateportion to you. If you are storing the times as well in the db then you will need to use the dartpart functionality as described by JamesT

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-03-05 : 17:40:39

How about:

SELECT tblNoticias.No_Id
FROM tblNoticias
WHERE DateDiff(dd, tblNoticias.No_InputFecha, GetDate())=0

JamesT
Yak Posting Veteran

97 Posts

Posted - 2002-03-05 : 17:41:56

My vote would be for:

SELECT tblNoticias.No_IdFROM tblNoticiasWHERE tblNoticias.No_InputFecha = convert(char(10), getdate(), 101)

It's much more simple that the previous example I posted.

Corobori
Posting Yak Master

105 Posts

Posted - 2002-03-05 : 17:46:11

quote:

Close, look on Books On Line at GetDate().

I knew GetDate()

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-03-05 : 23:59:03

Are you secretly an aussie, masquerading as a chilean?

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources