Date Question in SQL Statement

to strip time off, use

CONVERT(CHAR(10),yourdatetimecolumn,120)

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (course starts January 9 2005)

Or, a more generic way to chop dates into different bits, including day (what you asked), minute, month, year, or week:

DateAdd(d,DateDiff(d, 0, YourDate),0)

Just replace the d with mi, m, y, w...

-------------------------------------
• Every joy is beyond all others. The fruit we are eating is always the best fruit of all.
• It is waking that understands sleep and not sleep that understands waking. There is an ignorance of evil that comes from being young: there is a darker ignorance that comes from doing it, as men by sleeping lose the k

If donutman sees this..(other members comments are appreciated too)

DateAdd(d,DateDiff(d, 0, YourDate),0)

I'm a little worried about that zero-date concept. Because manual says parameter must be datetime and there is no implicit conversion between int and datetime. We know it works now but what happens in Yukon, or with some future mdac/oledb/whatever? Or does it say somewhere it complies with documentation?

So I'm thinking should I write

DateDiff(d, '1900-01-01', YourDate)

Cheers


[blue]Backup system is as good as the latest recovery[/blue]

yksvaan said:

there is no implicit conversion between int and datetime

Click to expand...

How about:
If they take that away with Yukon()
will be the least of our troubles.
-Karl



[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

I'm pretty sure Mircosoft won't play with zero date (1900-01-01). This could be kinda same as changing UNIX birth of Jesus (1970-01-01). This thing is simply too much used/referenced to be changed.

If this code happens server-side, I don't see how mdac/oledb can affect results.

Also: check "CAST and CONVERT" topic in BOL then scroll down to conversion matrix. Int/datetime combo is marked as "implicit conversion". Date types have very high conversion precedence (only sql_variant is ranked higher)... this conversion is safe IMO.

Anyway, explicit '1900-01-01' is definitely more readable so I'll use 0 instead

I'll use 0 because it might(?) save the extra step of forcing the function to recalculate it as a 0. Efficiency inches out clarity.
BTW, what are you doing up so early...can't sleep either?
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

Sorry folks, :
got my goggles on now and I see int -> datetime is implicit. My bad was I looked at datetime -> int.

Thanks

Cheers

efficiency inches out clarity" should not be the end of the story

every time you pull a hack like this --

... DateAdd(d,DateDiff(d,0,YourDate),0)

you owe it to your client or employer to document WTF it's doing and why you're using it

otherwise, the obfuscation level is unacceptably high

remember, you aren't writing SQL for yourself, you're writing it for the person who has to maintain your code









rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (course starts January 9 2005)

I thought we were writing code primarily to get work done, not for people.

Anyway, I don't think it's a hack. I think it's a clever mathematical manipulation.

it's too clever

but hey, to each his own, eh





rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (course starts January 9 2005)

Several points.
[ul][li]The efficiency inches out clarity quote referred to vongrunts use of '1900-01-01' instead of 0. I'm not certain how the programmers wrote the DateDiff and DateAdd functions, but I'm guessing that it's more efficient for the algorithm to have the zero instead of a string to start with. It was not a reference to the Convert method.

[/li]
[li]Neither method is obfuscatory, but neither methods is obvious. I read about the Convert method first and had no clue how it worked. First, I hadn't memorized the different styles and second, I didn't realize the trimming effect of char(10).

[/li]
[li]After you've seen either method several times they both become clear. If someone explains either method, it becomes clear. If you believe the Convert method is obvious without explanation, I suggest you show it to someone who hasn't seen it.

[/li]
[li]I prefer the DateAdd method NOT because it's clever, but because it's flexible. It solves many problems and consequently it's better for SQL programmers to become familiar with it...so that it does becomes clear to them. Other problems can then be solved quickly.

[/li]
[li]I dislike the Convert method because it requires the memorization of style numbers, if instead they had established meaningful labels I might feel differently. I also find very, very little use (unlike DateDiff and DateAdd) for Convert other than that one purpose. I think it's better practice to keep a datetime field as datetime within the recordset. Let the application format it. After all the user may want to change the sort direction.

[/li]
[li]If you feel its use is obfuscation or perhaps mathematical pedantry, then I'd say the same for 70% of the garbage code unintentionally written by the average programmer or perhaps all OOPs programming, respectively!

[/li]
[li]There are hundreds of programming tricks/gimmicks that programmers should learn. Are we to avoid them so complete beginners can read the code?

[/li][/ul]
-Karl


[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

by all means don't avoid them, embrace them!!

just document them where necessary

and of course i would document my CONVERT to CHAR(10) -- specifically with something like "strip time off datetime value for display purposes" or some similar remark

just because i gave a solution, and i'm also the one to raise the issue of clarity, does not imply that my solution was necessarily so much clearer as to obviate the need for documentation

anytime you strip the time off a datetime, it should be documented, not only for the technique being used (and you're right, both are weird to someone who hasn't seen them), but also for the reason why it's necessary

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (course starts January 9 2005)

Rudy, the reality is that I agree with you more than I disagree. I think many of the single query solutions that we offer posters are too clever. But that's what should be curtailed unless they too represent methodologies that can be easily learned and applied to other problems once learned.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

Karl,

Are you saying that the best answer isn't the most efficient one, but one that will be the most easily understood by beginners?

How will they ever learn the most efficient method if all we ever do is show the "easy" ones?

There is such a thing as too clever... like intentionally making something confusing (which I admit I have done). But anything that does the job more efficiently... it's not TOO clever, no matter how clever it is.

I'd like to see speed comparisons of the two methods of truncating the time portion from a date.

I don't agree. When a technique becomes too clever (not understood by coworkers even after an explanation), then it shouldn't be used. It would be better to write a multi-step process that makes the solution easier to maintain by others. This definition is a moving target. If you have many very-skilled individuals at your place of work, then it's reasonable to say the most efficient solution is the best. But if your organization is like most, then I'd argue for the easier approach. Of course, you can create scenarios for which my general rule would not apply, but business considerations are the final arbitrators in virtually all cases or you are being pedantic. IMHO
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

Speaking about obfuscation, once I managed code left by someone else. At few locations 2063-04-05 was used as 'zero' date for in-memory calculations. WTF? Everything worked OK, proprietary zero date was applied consistently, so I ignored this for a ... 15 minutes. On that hypothetical date Zefram Cochrane went warp 1 on booze. URGH!

Speed comparison: useless single-column SELECT query over 1,2M rows:

being pedantic, being clever, and being simple are like those three circles you see in Venn diagram examples -- what i aim for is the intersection of all three, as i am confident that they do intersect in most problems



rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (course starts January 9 2005)

A fivefold reduction in CPU time seems pretty compelling.

If it's not compelling, it sure makes a big circle. I'm blown away by the result. I'm very surprised that it's so much faster. So vongrunt, how about using the '1900-01-01' (clearer) instead of 0. Is it helpful, hurtful or indifferent?
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]