Select and smalldatetime TROUBLE!!!

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)

Select and smalldatetime TROUBLE!!!

Author

Topic

bbowser
Starting Member

43 Posts

Posted - 2007-06-20 : 14:48:08

I'm using the following code but still I get the time listed in the results. Can someone please help? Thanks in advance.

Select RECNO, DateAdd (Month, -12,Convert(smalldatetime,GetDate(),101)) AS LASTYEARTODAY

I've even tried using "Select dateadd(Month, -12, Convert(varchar(50), GetDate(), 101)) AS LASTYEARTODAY" and it still brings back a timestamp.

What am I doing wrong?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-06-20 : 15:07:12

What is your expected result?

Peter Larsson
Helsingborg, Sweden

bbowser
Starting Member

43 Posts

Posted - 2007-06-20 : 15:53:35

My results now are 06/20/2006 00:00:00
I don't want the time stamp to show in my results. The only thing I want to show up is the date without the time stamp. How do I remove the time stamp?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-06-20 : 15:59:15

You can't if you use smalldatetime or datetime data types.

Why not remove the timestamp in your application rather than in T-SQL though?

Tara Kizer
http://weblogs.sqlteam.com/tarad/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-06-20 : 16:01:30

Select RECNO, convert(varchar, DateAdd(Month, -12, current_timestamp), 101) AS LASTYEARTODAY

Peter Larsson
Helsingborg, Sweden

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-06-20 : 16:06:20

Notice how Peter's solution is using varchar.

Tara Kizer
http://weblogs.sqlteam.com/tarad/

bbowser
Starting Member

43 Posts

Posted - 2007-06-20 : 17:49:17

That did it Peso. Thanks everyone. I actually used GetDate() instead of the curent_timestamp though. I was actually thinking that I needed to move my convert function to be in front but just didn't do it. Thanks Again.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-20 : 19:23:48

quote:
I was actually thinking that I needed to move my convert function to be in front but just didn't do it

You should do the formatting in your front end and not in SQL.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-21 : 10:07:02

quote:
Originally posted by bbowser

My results now are 06/20/2006 00:00:00
I don't want the time stamp to show in my results. The only thing I want to show up is the date without the time stamp. How do I remove the time stamp?

Where do you want to show data?

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources