Today's date 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 2005 Forums

Transact-SQL (2005)

Today's date date

Author

Topic

snow12
Yak Posting Veteran

74 Posts

Posted - 2012-06-08 : 16:39:31

Hello:

I have the table having today's date date and also yesterday's data. How to get today's date data?

Thanks

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-06-08 : 17:20:14

[code]SELECT
<Column_List>
FROM
<Table_Name>
WHERE
<Date_Column> >= DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)
AND <Date_Column> < DATEADD(DAY, DATEDIFF(DAY, 1, GETDATE()), 0)[/code]
If that isn't what you are looking for, please see the links below.

http://www.sqlservercentral.com/articles/Best+Practices/61537/
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

snow12
Yak Posting Veteran

74 Posts

Posted - 2012-06-08 : 18:05:44

Thanks. My table like this:

name	identify	term	DateRequest
aaa	11		2	2012-06-07 00:00:00.000
aaa	11		3	2012-06-07 00:00:00.000
aaa	11		1	2012-06-07 00:00:00.000
aaa	11		4	2012-06-07 00:00:00.000
aaa	11		5	2012-06-07 00:00:00.000
aaa	11		6	2012-06-07 00:00:00.000
aaa	11		7	2012-06-08 14:39:12.240
aaa	11		8	2012-06-08 14:39:12.240
aaa	11		9	2012-06-08 14:39:12.243

I used: select * from name
where DateRequest >= DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)
AND DateRequest < DATEADD(DAY, DATEDIFF(DAY, 1, GETDATE()), 0)

return no result

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-09 : 02:22:15

[code]
select * from name
where DateRequest >= DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)
AND DateRequest < DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 1)
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

vinu.vijayan
Posting Yak Master

227 Posts

Posted - 2012-06-09 : 04:57:24

Visakh,

The OP just wants the Data for today's Date. Won't the following query suffice?...or is there something I am overlooking?


Select * From Ex
Where DATEPART(DD, CAST(DateRequest As Date)) = DATEPART(DD, Cast(GetDate() As Date))

N 28° 33' 11.93148"
E 77° 14' 33.66384"

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-06-09 : 05:28:13

quote:

Select * From Ex
Where DATEPART(DD, CAST(DateRequest As Date)) = DATEPART(DD, Cast(GetDate() As Date))

if getdate() is 2012-06-09

the query will return DateRequest dated 2012-06-09 and also DateRequest with any year or month as long as the DAY is 9. eg 2012-04-09, 2012-03-09 etc

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

vinu.vijayan
Posting Yak Master

227 Posts

Posted - 2012-06-09 : 05:47:22

quote:
Originally posted by khtan

quote:

Select * From Ex
Where DATEPART(DD, CAST(DateRequest As Date)) = DATEPART(DD, Cast(GetDate() As Date))

if getdate() is 2012-06-09

the query will return DateRequest dated 2012-06-09 and also DateRequest with any year or month as long as the DAY is 9. eg 2012-04-09, 2012-03-09 etc

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

Oooops!!!!....

That was soooooo simple.
How could I miss that???.....I need some sleep...

Thanks for explaining Khtan. I was blind not to see it.

N 28° 33' 11.93148"
E 77° 14' 33.66384"

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-06-09 : 07:47:29

One could use a slight variation on Vinu's query like this:

Select * From Ex
Where CAST(DateRequest As Date) = Cast(GetDate() As Date)

But, I suspect the unspoken reason why Lamprey and Visakh chose to post what they did has to do with efficiency. Even though their query is longer, that query may be able to use the index on the DateRequest column if one is available. With the query Vinu posted and I modified, SQL Engine would not be able to do that.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-09 : 11:18:15

quote:
Originally posted by sunitabeck

One could use a slight variation on Vinu's query like this:
Select * From Ex
Where CAST(DateRequest As Date) = Cast(GetDate() As Date)
But, I suspect the unspoken reason why Lamprey and Visakh chose to post what they did has to do with efficiency. Even though their query is longer, that query may be able to use the index on the DateRequest column if one is available. With the query Vinu posted and I modified, SQL Engine would not be able to do that.

yes...Exactly

thats reason why i prefer the open close interval comparison method over Vinus method

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

snow12
Yak Posting Veteran

74 Posts

Posted - 2012-06-11 : 12:05:12

Visakh:

Thank you very much!!

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-06-11 : 12:41:58

Also, DATE is a 2008+ data type, so you can't use it in 2005. :)

snow12
Yak Posting Veteran

74 Posts

Posted - 2012-06-14 : 15:42:39

Visakh:

Thank you very much for the help. I have the different question but it is relative for this one.

I have the data


name	identify	term		DateRequest
aaa	22		1		2012-06-13 15:18:11.457
aaa	22		2		2012-06-13 15:18:11.457
aaa	22		3		2012-06-13 15:18:11.457

I want to get all the data from yesterday, for example all data has 2012-06-13 no matter time 15:18:11.457.

I am running this query now. It is 14:34pm.

select * name where daterequest = DATEADD(day, DATEDIFF(day, 0, GETDATE()), -1)

However, I could not get any. Do you know how to achieve this one?

As always, your help is highly appreciated.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-14 : 15:46:27

still same method. use open close interval conditions


select * from name where daterequest >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), -1)
and daterequest < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

snow12
Yak Posting Veteran

74 Posts

Posted - 2012-06-14 : 16:34:49

Thank you very much! You are genius :)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-14 : 17:07:49

wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-06-15 : 00:44:05

quote:
Originally posted by sunitabeck

One could use a slight variation on Vinu's query like this:
Select * From Ex
Where CAST(DateRequest As Date) = Cast(GetDate() As Date)
But, I suspect the unspoken reason why Lamprey and Visakh chose to post what they did has to do with efficiency. Even though their query is longer, that query may be able to use the index on the DateRequest column if one is available. With the query Vinu posted and I modified, SQL Engine would not be able to do that.

Yes, the Query Optimizer can use the index, even if you CAST the DATETIME to DATE.
It is not that known, but check the Execution Plan.

Casting a DATETIME to DATE only truncates the last 4 bytes, so it works similiar to LIKE for varchars.

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources