Results 1 to 19 of 19

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

  1. #1

    Thread Starter
    Member
    Join Date
    Aug 2018
    Location
    France, North
    Posts
    61

    Resolved [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:
    1. --Ma fonction--
    2. DECLARE @Day integer    --
    3. DECLARE @Month integer  --The date from dd/mm/yyyy format
    4. DECLARE @Year integer   --
    5. DECLARE @Now date   --The date of today
    6.  
    7. SET @Day = (SELECT SUBSTRING(date,1,2) as extractstring FROM Donnees_MET)
    8. SET @Month = (SELECT SUBSTRING(date,4,2) as extractstring FROM Donnees_MET)
    9. SET @Year = (SELECT SUBSTRING(date,7,4) as extractstring FROM Donnees_MET)
    10. SET @Now = (SELECT CONVERT (date, GETDATE()))
    11.  
    12. 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 !

  2. #2
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    Re: Loop on SELECT RESULTS with a T-SQL Function

    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
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  3. #3

    Thread Starter
    Member
    Join Date
    Aug 2018
    Location
    France, North
    Posts
    61

    Re: Loop on SELECT RESULTS with a T-SQL Function

    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 !

  4. #4

    Thread Starter
    Member
    Join Date
    Aug 2018
    Location
    France, North
    Posts
    61

    Re: Loop on SELECT RESULTS with a T-SQL Function

    Thank you for the link, it look great !

  5. #5
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,418

    Re: Loop on SELECT RESULTS with a T-SQL Function

    *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: ***???
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  6. #6

    Thread Starter
    Member
    Join Date
    Aug 2018
    Location
    France, North
    Posts
    61

    Re: Loop on SELECT RESULTS with a T-SQL Function

    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 ?

  7. #7
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,418

    Re: Loop on SELECT RESULTS with a T-SQL Function

    https://www.w3schools.com/sql/sql_dates.asp
    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
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  8. #8
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,418

    Re: Loop on SELECT RESULTS with a T-SQL Function

    https://social.msdn.microsoft.com/Fo...um=transactsql
    SELECT CONVERT(INT, CONVERT(DATETIME,'2013-08-05 09:23:30'))
    SELECT CONVERT(INT, CAST ('2013-08-05 09:23:30' as DATETIME))
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  9. #9
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    Re: Loop on SELECT RESULTS with a T-SQL Function

    Quote Originally Posted by Macronaute View Post
    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
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  10. #10
    A SQL Server fool GaryMazzone's Avatar
    Join Date
    Aug 2005
    Location
    Dover,NH
    Posts
    7,495

    Re: Loop on SELECT RESULTS with a T-SQL Function

    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)
    Sometimes the Programmer
    Sometimes the DBA

    Mazz1

  11. #11
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,418

    Re: Loop on SELECT RESULTS with a T-SQL Function

    Quote Originally Posted by GaryMazzone View Post
    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
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  12. #12

    Thread Starter
    Member
    Join Date
    Aug 2018
    Location
    France, North
    Posts
    61

    Re: Loop on SELECT RESULTS with a T-SQL Function

    Quote Originally Posted by techgnome View Post
    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

  13. #13
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,900

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

    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.
    The best argument against democracy is a five minute conversation with the average voter - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  14. #14

    Thread Starter
    Member
    Join Date
    Aug 2018
    Location
    France, North
    Posts
    61

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

    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

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

  15. #15
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,900

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

    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.
    Last edited by FunkyDexter; Apr 15th, 2019 at 02:28 AM.
    The best argument against democracy is a five minute conversation with the average voter - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  16. #16

    Thread Starter
    Member
    Join Date
    Aug 2018
    Location
    France, North
    Posts
    61

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

    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 ? )

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

    Thank you by advance !

  17. #17
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,418

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

    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
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  18. #18
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,900

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

    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.

    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.
    Last edited by FunkyDexter; Apr 15th, 2019 at 07:17 AM.
    The best argument against democracy is a five minute conversation with the average voter - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  19. #19

    Thread Starter
    Member
    Join Date
    Aug 2018
    Location
    France, North
    Posts
    61

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

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

    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.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width