Computers are so literal

y tho

maybe wants to make it look like a fresh database? For curiosity's sake, I'd love to know also. I bet it some undocumented badly written function in Resources DB

Morning

My wife:

that's not an email or pizza starter though

No, it's not. Especially annoying because I did it in 3

sun rising over a forest

Can someone explain why cast(dateadd(day, 1, somedatetime) as date) is faster than dateadd(day, 1, cast(somedate as date))?

dbfiddle.uk/bDDBk7vz

You'd expect, given that a datetime is two integer fields internally, that it would be more complex to execute.

maybe the dateadd() has had some optimizations when the parameter is datetime2 but not when parameter is date

another word that I didn't know, jeez

@PaulWhite how?, jeezus

@Lamak if it makes you feel better, I didn't know yesterday's word (STAID).

@JoshDarnell it does

Apparently, I'm terrible at this compared to y'all

burger king

> my feelings

Burger peasant

@Charlieface sounds to me like there might be an extra implicit conversion (or two) in the slower bit. I'm not on a PC so I can't actually tell.

@ypercubeᵀᴹ It's datetime not datetime2, interestingly enough for datetime2 the opposite is true dbfiddle.uk/FxR7jDZv

Maybe @SeanGallardy can chime in with some sauce code.

sean is famous for pasting source code into chat

Sean "The Paste Master"

not master paster

are we sure

we are not

> Create linkied server encrypted from sqlserver 2019 to 2008r2

there's a lot going on in that one

i edited it and then saw your comment

oh well

I opted for vtc as too localized.

same

this seems like a shopping list question unless i'm missing something

So what's everyone's plans for the weekend?

@ErikDarling Agreed other than the answer may simply just be a SQL Agent Job, in which case does it make sense to not close?

Btw re: your comment on using a hex value. I'm a noob with that stuff. Is BINARY(4) actually storing the data as true hex? Does the code you wrote work if the input value changes length?

@Charlieface The difference is in compilation time, not execution time

@Zikato The difference rises as the table gets larger so unlikely. And if there are no rows then its immediate. dbfiddle.uk/MSS2tJvW

"great!"

@Lamak just a lucky guess. She was as surprised as anyone

@PaulWhite everyday I'm more aware of the limits in my english vocabulary

would've never been able of even having a lucky guess...mostly making up words that I think exist

would never lucky guess words think exist

where would you go to ask a question on a hardware spec

serverfault?

consumer spec, displayport/hdmi

need a pro on both on those techs

electronics.stackexchange.com/questions/ask

maybe that one

Actually I think I follow you better. If the hex is actually hex, by standard, it is little-endian, so OP's goal is kinda silly.

wut...

what standard?

The standard of how hex is designed. Perhaps not best word?

This one's quite a question to effectively just ask for Microsoft's feedback website lol: dba.stackexchange.com/q/325146/150011

@Charlieface good question

so what i think is happening there is that dateadd is coercing both arguments to datetime2. and your cast, is adding more work, and not doing anything.

actually it's worse...

> The return value data type for this method is dynamic. The return type depends on the argument supplied for date. If the value for date is a string literal date, DATEADD returns a datetime value. If another valid input data type is supplied for date, DATEADD returns the same data type. DATEADD raises an error if the string literal seconds scale exceeds three decimal place positions (.nnn) or if the string literal contains the time zone offset part.

that's from the documents of dateadd

I see it's amateur hour again

so when you coerce d to date you force dateadd to return a date. rather than a datetime2(7) which sysdatetime returns.

so dateadd(day, 1, cast(d as date)) makes dateadd return a date while dateadd(day, 1, d) returns a datetime2(7)

You're confusing the timing with the demo

@PaulWhite yes hello

@Charlieface Skipping a lot of details, @ypercubeᵀᴹ is basically correct aside from the usual typo. The call sequence for the slow case is:

sqlTsEs!CTEsConvert<61,40>::RsltConvertArg (datetime to date)
sqlTsEs!DtoDateaddI4I4Dto
sqlTsEs!CDatetimeOffset::Serialize
sqlTsEs!CDatetimeOffset::Deserialize
sqlTsEs!CDatetimeOffset::DateAdd
sqlTsEs!CDatetimeOffset::GetLocalDatetime2
sqlTsEs!CDatetime2::AddTimeOffset
sqlTsEs!CTime::AddTimeOffset
sqlTsEs!CTime::Convert
sqlTsEs!CDatetimeOffset::Serialize
sqlTsEs!CDatetimeOffset::Deserialize

For the faster expression it is:

sqlTsEs!D8DateaddI4I4D8
sqlTsEs!SQLDATE::DateAdd
sqlTsEs!CTEsConvert<61,40>::RsltConvertArg (datetime to date)

The older datetime type has some optimizations that are preserved for backward compatibility. The newer types tend to be more accurate and consistent, but can be slower. As a reminder of some of the more recent adjustments see docs.microsoft.com/en-us/troubleshoot/sql/database-engine/…

Sometimes, the differences are painfully obvious in the execution plan as in sqlsunday.com/2022/10/10/datediff-performs-implicit-conversions but sometimes they're not (as here).

In short, dates and times were a mistake

@Charlieface As an aside, you can keep working with datetime in both cases by switching to DATETRUNC dbfiddle.uk/sy595lb4

In both cases, you get the following calls, just in a slightly different order:

sqlTsEs!D8DateTruncI4D8
sqlTsEs!SQLDATE::DateTrunc
sqlTsEs!DaysFromADToDateparts
sqlTsEs!D8DateaddI4I4D8
sqlTsEs!SQLDATE::DateAdd

In principle, you can do the same thing with DATETIMEFROMPARTS but you lose more than you gain from all the calls to extract the year, month, and day components.

I had a demo many years ago of a trivial CLR function outperforming a single xxxFROMPARTS call.