date time - 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)

date time

Author

Topic

GeoffreyP
Starting Member

9 Posts

Posted - 2010-02-15 : 02:52:33

I use need to extract data using a start date like the following:
Select
@Date = getdate()-2

Gives me the result: Feb 13 2010 9:49AM
How do I Get the Time to always be 0:00 AM

Kristen
Test

22859 Posts

Posted - 2010-02-15 : 03:03:46

SELECT @Date = DATEADD(Day, DATEDIFF(Day, 0, GetDate())-2, 0)

GeoffreyP
Starting Member

9 Posts

Posted - 2010-02-15 : 03:12:18

Thanks Will try this

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-02-15 : 03:56:50

Try this too

select convert(varchar,GETDATE()-2,101)

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

Kristen
Test

22859 Posts

Posted - 2010-02-15 : 04:04:35

"select convert(varchar,GETDATE()-2,101)"

Do not use this.

It required a conversion from DateTime datatype to String - and then be an implicit Cast back to datetime - which may well fail if the default locale is not mm/dd/yyyy - or the application is moved to a server in the future with a different locale, or the current server locale is changed.

Apart from the potential bugs with implicit date conversion, the two datatype conversiosn are very expensive (relative to the integer arithmetic of DATEADD / DATEDIFF)

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-02-15 : 04:07:06

quote:

Apart from the potential bugs with implicit date conversion, the two datatype conversiosn are very expensive (relative to the integer arithmetic of DATEADD / DATEDIFF)

Vital information thanks!

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

persiangulf098
Starting Member

10 Posts

Posted - 2010-02-15 : 04:55:44

I cant understand -2 in this ==>getdate()-2

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-15 : 05:03:39

quote:
Originally posted by persiangulf098

I cant understand -2 in this ==>getdate()-2

2 days earlier than Today

Madhivanan

Failing to plan is Planning to fail

persiangulf098
Starting Member

10 Posts

Posted - 2010-02-15 : 05:11:29

thanks

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-15 : 05:54:51

quote:
Originally posted by senthil_nagore

Try this too

select convert(varchar,GETDATE()-2,101)

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

I told you sometimes back "Work on DATEs and not VARCHARs"

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources