[RESOLVED] Loop on SELECT RESULTS with a T-SQL Function

Hi, I am trying to get the number of day between two dates. (A date and today's date)

The date is a string in the DD/MM/YYYY format.

I encoded the following function in SQL SSMS but I have a issue, as I select all my Date column, I got an error when I execute.

SQL Code:

--Ma fonction--
DECLARE @Day integer    --
DECLARE @Month integer  --The date from dd/mm/yyyy format
DECLARE @Year integer   --
DECLARE @Now date   --The date of today
 
SET @Day = (SELECT SUBSTRING(date,1,2) as extractstring FROM Donnees_MET)
SET @Month = (SELECT SUBSTRING(date,4,2) as extractstring FROM Donnees_MET)
SET @Year = (SELECT SUBSTRING(date,7,4) as extractstring FROM Donnees_MET)
SET @Now = (SELECT CONVERT (date, GETDATE()))
 
SELECT datediff(DAY, @Year-@Month-@Day, @Now)   --Number of days between the date and today

I don't know how I can loop on each result one by one to return the number of day between the date and today. (This is in order to send a notification after 30 days in a vb.net app)

Thank you for your help !

Why is the date a string and not in a Date? Thats where the problem really lies. The target date should be a Date or a DateTime object, not a string...


That said... this: @Year-@Month-@DAY is wrong because those variables are simply integers, so it sees it as a mathematical formula the same as x - y - z ... which is clearly not what you want. You may want to look at using DateFromParts instead..

But really, your date value should be a Date or a DateTime datatype to begin with in the first place.


-tg

Yes you are right, it should be a date but, I created it like that but it wasn't planned to work with this information.
But I arrived to the same conclusion, the easiest way will be to convert my table in date.

By the way, I tested that (x-y-z) and it gave me the right answer so it works, but I can't do it to all the result.

Anyway, as you said I will try to convert my string table into a date table.

Thank you !

Thank you for the link, it look great !

*sigh*
and if people just knew, that a Field of Type Date is basically an integer, it would be so much easier to just do
SELECT (CONVERT(INT, GETDATE())-CONVERT(INT, MyDateField))) AS Difference FROM MyTable.....

Nevermind that the DateDiff-Function is still the right way to go, but everytime i see such convoluted contorted maneuvers, i ask myself: ***???

Hi, I didn't understood everything Zvoni but thank you for your return.

As you are here, I have another question about datetime, I just want a short date but when I use DATEFROMPARTS I have the hours too. Do you know how to keep only year-month-day please ?

https://www.w3schools.com/sql/sql_dates.asp

Quote:

---

SQL Server comes with the following data types for storing a date or a date/time value in the database:

• DATE - format YYYY-MM-DD
• DATETIME - format: YYYY-MM-DD HH:MI:SS
• SMALLDATETIME - format: YYYY-MM-DD HH:MI:SS
• TIMESTAMP - format: a unique number

---

https://social.msdn.microsoft.com/Fo...um=transactsql

Quote:

---

SELECT CONVERT(INT, CONVERT(DATETIME,'2013-08-05 09:23:30'))
SELECT CONVERT(INT, CAST ('2013-08-05 09:23:30' as DATETIME))

---

Quote:

---

Originally Posted by Macronaute

Hi, I didn't understood everything Zvoni but thank you for your return.

As you are here, I have another question about datetime, I just want a short date but when I use DATEFROMPARTS I have the hours too. Do you know how to keep only year-month-day please ?

---

Don't confuse the value with the format, which is only for display... if you're seeing 00:00:00.0000 for the time portion, that's fine... that's what you should be seeing... that's midnight... that means there's no time on it... and that's perfectly fine... too often people get hung up on what the date looks like rather than concentrating on what it's value is... and that's the wrong thing to do... it doesn't matter what it looks like... what matters is the value.

-tg

Dates and datetime are actually decimal values not integers. An NO Use the proper data type for what you want don't convert a date to numeric formats leave it as a date (or datetime)

Quote:

---

Originally Posted by GaryMazzone

Dates and datetime are actually decimal values not integers.

---

Quote:

---

Originally Posted by MySelf

....that a Field of Type Date is basically an integer,....

---

There: DATE (not DateTime)

That, and i never said to change the DataType of the Field, just to use the fact, that you can do "normal" arithmetic calculations on it

Quote:

---

Originally Posted by techgnome

Don't confuse the value with the format, which is only for display... if you're seeing 00:00:00.0000 for the time portion, that's fine... that's what you should be seeing... that's midnight... that means there's no time on it... and that's perfectly fine... too often people get hung up on what the date looks like rather than concentrating on what it's value is... and that's the wrong thing to do... it doesn't matter what it looks like... what matters is the value.

-tg

---

Thank you Techgnome for your explanation, this is exactly what I see on SSMS.
I will continue on this way, I mark the thread resolve ;)

I use the Dates as Integers trick all the time but I would sound a slight note of caution. It's not documented behaviour so MS could, in theory, change it and break your application. They almost certainly won't...but they could. Generally, I use stuff like that if I'm writing throw away scripts but I try to use a more formal approach if I'm putting something into production.

The proper way of handling this would be to convert the string value into a date type and then use the datediff function:-

Code:

---

DateDiff(Day, Convert(Date, extractstring, 103), GetDate())

---

I've used format 103 which assumes dd/mm/yyyy but you might want different formats (e.g. 104) depending on what character your using to divide the parts of the date in the string. See here.

Thank you for your tip, this is interesting, but I just finish to transform all my string date into datetime so ... maybe for my next project ! ;)
The format 103 is the one I was used too.

I'm wondering, did you already have a issue like that because of a MS update? Because it looks impossible to me that they could do this to many applications :ehh:

In any case, thank you because in one line you replace all my SQL function of my first post !

Quote:

---

did you already have a issue like that because of a MS update?

---

No, I've never had an MS change break my apps yet. MS are very good at maintaining backward compatibility and when they do need to drop something they give very long deprecation windows (Seriously, there's stuff in 2018 that was deprecated back in 7).

However, I do think it's particularly important to be aware of the risks whenever using an undocumented feature. If you can avoid using them in production code you should but some of those tricks offer such strong performance pay offs that it's worth the risk.

alright, but I have some questions about what you said :

Even MS give long depreciation windows when update, how do you know that one of your function will be damage ? (You need to read what will change when they publish what they will change ? :ehh:)

I'm not sure to understood what is a undocumented feature, for me they are all documented in msndn, no ? :ehh:
I understood what you said about potentials risks and gains, but how do you recognize an undocumented feature ?

Thank you by advance !

I don't think, that the "feature" that a Date (or DateTime) is basically a numeric value (Integer in case of Date, Float/Real/Double in case of Date and Time) is going to be broken in any forseeable future.
I base my assumption on the fact, that every programming language has a Function to return the current Date, which is never a string, but a numeric value.

Another reason, why i don't think it's going to happen (albeit i see it as possible, that the Float/Real/Double-Representation of Time in a DateTime might be abandonned one day) is actually the concept of Time
(or better said: the passage of Time):
What is actually a day?
What does actually January 3rd 2019 - 6AM mean?
it means: "2 full days since the beginning of the year have passed, and 6 hours of the third day have passed"
Remembering, that above example can also be represented by the phrase:
"54 hours since the beginning of the year 2019 have passed"

It's the same for every Unit-Aggregation:
gramms to Kilogramms to Tons
centimeter to meter to kilometer to lightyear
seconds to minutes to hours to days to months to years
A Date is just an aggregated Representation of Time

Quote:

---

how do you know that one of your function will be damage ?

---

The best way is to use the upgrade advisor. The exact process changes slightly between versions of SQL Server but basically, you install the version of SQL Server you want to upgrade to, point it at your database and then run the upgrade advisor. It will churn away for a while then spit out a report telling you everything that's been deprecated and/or is going to break.

This only works if you've got your code in the database though. if you are building sql strings in a client App (ie VB) then I don't think there really is a short cut. You have to check the changes list and work through your own code. There may be some third party products that would help but I'm not aware of them.

Note that you're only likely to experience issues when moving between versions of sql server, not as a result of MS just spontaneously deciding to change an existing version. So sanity checking and testing your code is really just part of your upgrade process.

Quote:

---

for me they are all documented in msndn, no ?

---

No. There's lots of stuff in sql server that just works but isn't technically part of the specification. For example, here's the docs page for the datetime type. Note that it doesn't say anywhere on there that a datetime is implemented as a floating point or that straight arithmetic will work on it. It is a floating point and arithmetic does work against it, but only by coincidence, not by contract. The contract MS offers you is that it's a datetime and it will work correctly as a datetime as long as you treat it as a datetime. If you treat it as a number it's not guaranteed to work.

That said, MS haven't changed the implementation of a datetime since at least version 7 (that's over 2 decades) and it's hard to see why they ever would change it. Perhaps a pure binary representation might provide better performance but I doubt it, the underlying arithmetic is all done in binary anyway. I guess they might be able to cram it into slightly smaller storage space but we've got BIG disks and memory to play with these days so that's unlikely to be much of a driver.

So it's extremely unlikely that MS are going to change that implementation... it's just not guaranteed. That's why I say avoid exploiting undocumented behaviours in production code where possible but accept that there are times when you're going to end up using them because the performance payoffs outweigh the risks.

Thank you so much for your time and your explanation @FunkyDexter, I understand a bit more what you were talking about ! ;)

Quote:

---

if you are building sql strings in a client App (ie VB) then I don't think there really is a short cut

---

Yes, this is what I do, but this is interesting to know that some tools like that exists. :D