Fetching date part from GetDate()

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

Fetching date part from GetDate()

Author

Topic

usr123
Starting Member

12 Posts

Posted - 2010-06-03 : 06:44:23

How can i get date part from GetDate() function?
I have a date column in the table which has date n time part, now i need to serach all records for today only, alos i need to put this in a batch file. I need example to fetch date part from getdate() function and use it as search criteria in the table that will give me all the records entered today ignoring time part.
ANy ideas...?

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-06-03 : 06:57:09

The usual way is to add the number of days from a particular date in the past.

For instance this will effectively strip the time portion from GETDATE()


SELECT DATEADD(DAY, DATEDIFF(DAY, '19000101', GETDATE()), '19000101')

So if you wanted to bring back all rows in a table where the column that holds a datetime is in today you can do something like this:


SELECT
    *
FROM
    [yourTable]
WHERE
    [dateColumn] >= DATEADD(DAY, DATEDIFF(DAY, '19000101', GETDATE()), '19000101')
    AND [dateColumn] < DATEADD(DAY, DATEDIFF(DAY, '19000101', GETDATE()) + 1, '19000101')

Now this might look complicated to do when you have functions like DATEDIFF but this way can use any index on the date column but if you did:


SELECT
    *
FROM
    [yourTable]
WHERE
    DATEDIFF(DAY, [dateColumn], GETDATE()) = 0

Then you could not use any index on [dateColumn]

Hope this helps.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

usr123
Starting Member

12 Posts

Posted - 2010-06-03 : 07:10:48

Thanks For that, it works!!

Cheers!!

usr123
Starting Member

12 Posts

Posted - 2010-06-04 : 08:34:46

Hi Again,

The code above doesnt give records for up to the minute, e.g. if i have recods coming through today every 2 min, and when i search for today's records using above code, it only gives me records 2 hours from now or something like that, but not up to the record just got created a minute ago.
Do we know why?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-06-04 : 08:37:36

Can you show the query?

No, you're never too old to Yak'n'Roll if you're too young to die.

usr123
Starting Member

12 Posts

Posted - 2010-06-04 : 08:41:21

1 sec

usr123
Starting Member

12 Posts

Posted - 2010-06-04 : 08:48:48

Its something like:

SELECT TOP 100 PERCENT dbo.tbl1.CreatedDate, dbo.tbl1.Title, dbo.tbl1.FirstName
FROM dbo.tbl1 INNER JOIN
dbo.tbl2 ON dbo.tbl1.CountryID = dbo.tbl2.CountryID INNER JOIN
dbo.tbl3 ON dbo.tbl1.ProductID = dbo.tbl3.ProductID INNER JOIN
dbo.tbl4 ON dbo.tbl2.RegionID = dbo.tbl4.RegionID
WHERE (dbo.tbl1.ID = 178) AND (dbo.tbl1.CreatedDate >= DATEADD(DAY, DATEDIFF(DAY, '19000101', GETDATE()), '19000101'))
AND (dbo.tblMembership.CreatedDate < DATEADD(DAY, DATEDIFF(DAY, '19000101', GETDATE()) + 1, '19000101'))
ORDER BY dbo.tbl4.RegionID

usr123
Starting Member

12 Posts

Posted - 2010-06-04 : 09:54:16

Any ideas on this one?

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-06-04 : 10:14:30

quote:
Originally posted by usr123

Its something like:

SELECT TOP 100 PERCENT dbo.tbl1.CreatedDate, dbo.tbl1.Title, dbo.tbl1.FirstName
FROM dbo.tbl1 INNER JOIN
dbo.tbl2 ON dbo.tbl1.CountryID = dbo.tbl2.CountryID INNER JOIN
dbo.tbl3 ON dbo.tbl1.ProductID = dbo.tbl3.ProductID INNER JOIN
dbo.tbl4 ON dbo.tbl2.RegionID = dbo.tbl4.RegionID
WHERE (dbo.tbl1.ID = 178) AND (dbo.tbl1.CreatedDate >= DATEADD(DAY, DATEDIFF(DAY, '19000101', GETDATE()), '19000101'))
AND (dbo.tblMembership.CreatedDate < DATEADD(DAY, DATEDIFF(DAY, '19000101', GETDATE()) + 1, '19000101'))
ORDER BY dbo.tbl4.RegionID

This looks fine...atleast to me.
Can you provide some sample data and tell us how it isn't working as expected.

EDIT: Could it be possible that the latest record is not fetched because of one of the JOIN conditions and not because of the WHERE condition?

usr123
Starting Member

12 Posts

Posted - 2010-06-04 : 10:34:55

If i remove second where condition,i get all the records up to the minute.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-06-04 : 10:39:18

OK THEN...Can you provide some sample data and tell us how it isn't working as expected.

usr123
Starting Member

12 Posts

Posted - 2010-06-04 : 10:57:17

With second where clause

04/06/2010 13:58:46 Mr Test1

Without second where clause

04/06/2010 15:54:50 Mr Test2

usr123
Starting Member

12 Posts

Posted - 2010-06-04 : 10:58:40

Sorry these are the last records.
Created Date Title First Name
04/06/2010 13:58:46 Mr Test1

Created Date Title First Name
04/06/2010 15:54:50 Mr Test2

usr123
Starting Member

12 Posts

Posted - 2010-06-07 : 04:51:49

Any ideas on this one?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-06-07 : 05:11:54

If there are no records with a CreatedDate lying in the future then just remove the second part of your where clause.

No, you're never too old to Yak'n'Roll if you're too young to die.

usr123
Starting Member

12 Posts

Posted - 2010-06-07 : 07:50:50