Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Join Date
    Nov 2006
    Location
    Quebec
    Posts
    172

    Question Unanswered: How to ensure date are handle as it should

    I was wondering if SQL SERVER 2005 has a function to specify what date format you are using ex: in a where clause.

    I'm looking for something like this

    WHERE FORMAT(my_date, "yyyy/mm/dd")<= @my_date

    So what's in red is what I'm looking for.

    Thanks in advance!

    Or Tho
    Less is more.
    How long is now?
    http://www.lesouterrain.com

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    If you do that you are making any indexes on my_date unusable. Try googling "sargable" and "SQL Server" to get more info.

    If @my_date is a datetime variable (and it should be) then there is no such thing as date format. Date format is only how you want to display a date when you convert it to a string i.e. it is a display issue, nothing more, nothing less.

    HTH

  3. #3
    Join Date
    Nov 2006
    Location
    Quebec
    Posts
    172
    But in my app I have both french and english users.

    And date format is not the same for both.

    So how sql will know which date format to use?
    Less is more.
    How long is now?
    http://www.lesouterrain.com

  4. #4
    Join Date
    Nov 2006
    Location
    Quebec
    Posts
    172
    My_date isin't a datetime because the real var is:

    @myFilters AS varchar(8000)

    and the content of this var looks like this:

    @filters=N'AND (mo.DSCRIPTN LIKE(''BAG-08-00%'')) AND (i.ITEMNMBR>=''A'') AND (i.ITEMNMBR<=''CZ'') AND (mo.STRTDATE>=''2008/01/16'')'
    Less is more.
    How long is now?
    http://www.lesouterrain.com

  5. #5
    Join Date
    Nov 2006
    Location
    Quebec
    Posts
    172
    It's ok I found my way:

    @filters=N'AND (mo.DSCRIPTN LIKE(''BAG-08-00%'')) AND (i.ITEMNMBR>=''A'') AND (i.ITEMNMBR<=''CZ'') AND (mo.STRTDATE>=CONVERT(datetime, ''21/01/2008'', 101))'
    Less is more.
    How long is now?
    http://www.lesouterrain.com

  6. #6
    Join Date
    Nov 2005
    Posts
    122
    For the tenth millionth time, dates are not stored in SQL Server in a specific format. Don't confuse the display of a date with the way SQL Server stores it. Please look up "Datetime datatype" in BOL and UNDERSTAND how it is stored.

    Always, and I repeat ALWAYS, use parameterized queries, and use the correct datatype. The is for security reasons, but also, as you are experiencing, to avoid the kind of problems you have right now. If you don't follow this rules I will come and hack your system.

  7. #7
    Join Date
    Nov 2006
    Location
    Quebec
    Posts
    172
    SQL doesn't but if you specify a date stored in a string (char), we can not be sure that the month and the day will be taken in the right order unless we convert it into a date... that's the whole point.

    Thank you anyway.
    Less is more.
    How long is now?
    http://www.lesouterrain.com

  8. #8
    Join Date
    Nov 2005
    Posts
    122
    Quote Originally Posted by ortho
    SQL doesn't but if you specify a date stored in a string (char)...
    Why would you do that from a client application? Don't you use parameterized queries?

    And if you do it yourself from SSMS then I guess you know the format to put in the string.

  9. #9
    Join Date
    Nov 2006
    Location
    Quebec
    Posts
    172
    Quote Originally Posted by kaffenils
    Why would you do that from a client application? Don't you use parameterized queries?

    And if you do it yourself from SSMS then I guess you know the format to put in the string.
    Because all of my WHERE criterias are stored in the same @var, and this is the client app that build it so...
    Less is more.
    How long is now?
    http://www.lesouterrain.com

  10. #10
    Join Date
    Nov 2005
    Posts
    122
    Quote Originally Posted by ortho
    Because all of my WHERE criterias are stored in the same @var, and this is the client app that build it so...
    And who made the client? Building and executing SQL statements like this is really, really, REALLY dangerous, and will lead to a lot of problems (or challenges if you like ) as you have experienced.

  11. #11
    Join Date
    Nov 2006
    Location
    Quebec
    Posts
    172
    Well I make it, but I found it safe.... Why do you think it's so dangerous?
    Less is more.
    How long is now?
    http://www.lesouterrain.com

  12. #12
    Join Date
    Nov 2005
    Posts
    122
    SQL Injections and wrong data (imaginge this in when reporting financial data):

    SQL injections is a "technique" used to insert and execute arbitrary SQL commands. Usually due to not using parameters when executing SQL commands.

    Let's say you have a textbox named Description in your application. The value in this textbox is put into a string variable like this:
    Sql="AND (mo.DSCRIPTN LIKE('" & Description.Text & "%'))" which then is executed. Imagine what will happen if I type ')); delete from AnExistingTable--. This is just one example of an sql injection. Google "sql injection" and you'll find lots more.

    This one is quite funny http://xkcd.com/327/

  13. #13
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Why not to do this:
    * Damn - I had a great link to a SQL Injection example but it has been withdrawn - http://www.rockyh.net/AssemblyHijack...Hijacking.html
    Just google SQL Injection - the point is you are totally exposing your network with this sort of code.

    How to avoid doing this:
    http://weblogs.sqlteam.com/jeffs/jef.../21/10728.aspx

  14. #14
    Join Date
    Jul 2003
    Location
    Amsterdam, Nederland
    Posts
    450

    Smile

    Quote Originally Posted by ortho
    But in my app I have both french and english users.

    And date format is not the same for both.

    So how sql will know which date format to use?
    SO there your answer : But in MY APP.....
    If they must put in a date use a datepicker- calender controle
    they push a button (1 may 2008)
    you get the date of your controle.
    then you give it to your SQL as YOU want it
    yyyy-mm-dd or ddMMyyyy or what ever format.
    Greetz Marvels -^.^-
    Developments : VB4 Through .Net; Basic; DOS ; CNC ; Sinclair
    Databases : SQL Server Through 2005; Access 3 Through 2003 ; Oracle 8 & 9.i ;
    OS : Win 3.11 Through XP ; NortonComander ; DOS

  15. #15
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912

Posting Permissions

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