I want to return Only Date withut Time

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

SQL Server Development (2000)

I want to return Only Date withut Time

Author

Topic

ya3mro
Starting Member

37 Posts

Posted - 2006-06-15 : 05:59:27

in my example i have table has column called DATE
and when i do :
Select Distinct Date from tablename
i have ::

09/06/2006 07:00:00
09/06/2006 10:00:00
10/06/2006 04:00:00
10/06/2006 07:00:00
10/06/2006 10:00:00
11/06/2006 04:00:00
11/06/2006 07:00:00
11/06/2006 10:00:00

and i want to Select Distinct Date without Time
to be showed as
09/06/2006
10/06/2006
11/06/2006

PLZ Help Me

How I Came To Islam?
http://english.islamway.com

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-06-15 : 06:07:23

Where do you want to show the converted dates?
If you use Front end application, do formation there
Otherwise look for Convert function in sql server help file

Madhivanan

Failing to plan is Planning to fail

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-06-15 : 06:20:57

[code]select dateadd(day, datediff(day, 0, datecol), 0)[/code]

ya3mro
Starting Member

37 Posts

Posted - 2006-06-15 : 07:07:25

no i want to return from SQL as distinct Date

How I Came To Islam?
http://english.islamway.com

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-06-15 : 07:11:48

[code]select distinct dateadd(day, datediff(day, 0, datecol), 0)[/code]

ya3mro
Starting Member

37 Posts

Posted - 2006-06-15 : 07:28:29

thanx KHtan and i also try this ::

SELECT DISTINCT CAST(SUBSTRING(CAST(DateCol AS varchar), 0, 10) AS Datetime) AS DateResult
FROM tableName

but which is better this OR
select dateadd(day, datediff(day, 0, datecol), 0)

How I Came To Islam?
http://english.islamway.com

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-06-15 : 10:35:41

the dateadd(), datediff() is faster.
Also refer to http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=64762 for more information on datetime

sqldev80
Yak Posting Veteran

68 Posts

Posted - 2006-06-15 : 12:27:49

SELECT CONVERT(VARCHAR(10), GETDATE(), 126)

you can put the coulmn name instead of the getdate()
Select Distinct CONVERT(VARCHAR(10), GETDATE(), 126) from table name

Hope this works.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-06-16 : 02:42:49

quote:
Originally posted by ya3mro

thanx KHtan and i also try this ::

SELECT DISTINCT CAST(SUBSTRING(CAST(DateCol AS varchar), 0, 10) AS Datetime) AS DateResult
FROM tableName

but which is better this OR
select dateadd(day, datediff(day, 0, datecol), 0)

How I Came To Islam?
http://english.islamway.com

The second method is best to use if there is index on the date column
If you use front end application format the date there

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources