Date function - 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

General SQL Server Forums

New to SQL Server Programming

Date function

Author

Topic

thehandsomecode
Yak Posting Veteran

51 Posts

Posted - 2007-05-08 : 09:04:24

I need to sort some data by date: my date format is looks like this
5/7/2007 11:38:54 AM. but i need to sort sort by just the first part "5/7/2007" how do i achieve this.?

Melvin Felicien
IT Manager
DCG Properties Limited

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-05-08 : 09:13:28

How are your dates stored?
As DATETIME or VARCHAR?

Peter Larsson
Helsingborg, Sweden

thehandsomecode
Yak Posting Veteran

51 Posts

Posted - 2007-05-08 : 09:19:35

They are stored as datetime

Melvin Felicien
IT Manager
DCG Properties Limited

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-05-08 : 09:22:01

SELECT * FROM Table1
ORDER BY Col1

is sufficient. If you cut the datetime column and use only the date part, the records will, or will not, be randomized.

Peter Larsson
Helsingborg, Sweden

thehandsomecode
Yak Posting Veteran

51 Posts

Posted - 2007-05-08 : 09:26:16

My problem is i dont know how to cut only use the date part. i am an IT Manager who has just been giving an sql burden to carry

Melvin Felicien
IT Manager
DCG Properties Limited

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-05-08 : 09:38:31

What if you have multiple records with same date but different time. If you omit time part in such case, what should be the order for those records, because date will be same for all of them?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-05-08 : 09:46:36

thehandsomecode -- I think you are not getting it. Think of a date with a time as like a number, with a decimal place. got it? Now, what if I have this:

1.34
2.55
3.95
4.12
4.66

And you say to me "I want to sort by the integer portion only, and ignore the decimal place". Do you see that the results remain the same whether or not you include the decimal??? The only difference is that by including the decimal places, 4.12 will ALWAYS sort be 4.66, but if you ignore that, then they are both 4, and they will sort either way.

Now, if you want to return only 1 row per day, then that is another story, and different from saying you want the data sorted a particular way.

- Jeff
http://weblogs.sqlteam.com/JeffS

thehandsomecode
Yak Posting Veteran

51 Posts

Posted - 2007-05-08 : 09:51:22

Thank you. yes want i want to do is return one row per day.

Melvin Felicien
IT Manager
DCG Properties Limited

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-05-08 : 10:13:32

Use Dateadd(day,datediff(day,0,datecol),0)

or

post some sample data with expected result

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-05-08 : 10:35:52

You want some AGGREGATION?

SELECT DATEADD(day, DATEDIFF(day, 0, Col1), 0) AS theDay,
SUM(Col2)
FROM Table1
GROUP BY DATEADD(day, DATEDIFF(day, 0, Col1), 0)

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources