SQL Server Performance

DateTime Error

Discussion in 'T-SQL Performance Tuning for Developers' started by Toni, May 13, 2006.

  1. Toni New Member

    I have a function that is based aounr the input of parameters. The last remaing issue is that I am required to enter the data into the parameter field as mm/dd/yyyy. I want to be able to enter the data as dd/mm/yyyy. I have tried to use

    WHERE (CONVERT(datetime,src_terrier.datadate,103) = @dt_src_date) AND..........

    But this just throws an error "Msg 8114, Level 16, State 1, Procedure spWTRalldatareportsummary, Line 0
    Error converting data type nvarchar to datetime."

    The execution line I am using is

    USE [DashboardSQL-2K5]

    DECLARE@return_value int

    EXEC@return_value = [dbo].[spWTRalldatareportsummary]
    @dt_src_date = N'28/04/2006',
    @chr_div = NULL,
    @vch_portfolio_no = NULL,
    @vch_prop_cat = NULL

    SELECT'Return Value' = @return_value


    Anybody got any ideas as to what I have done wrong? I have also tried it without the N just before the date and get a varchar version of the same error.

    Thanks in advance

    Toni Chaffin
    aka Toni
  2. Twan New Member

    Hi ya,

    have you tried calling set dateformat dmy before the procedure call?

  3. FrankKalis Moderator

  4. Toni New Member

    Thank you, I shall read that article now.


    Toni Chaffin
    aka Toni
  5. Madhivanan Moderator

    Always use Universal format yyyy-mm-dd or yyyymmdd


    Failing to plan is Planning to fail
  6. Twan New Member

    hi ya,

    yes agree with other that if you have a choice then
    1. use datetime/smalldatetime for all date variables
    2. let the presentation software deal with displaying it the right way and allow for user entry the right way

    however if you can't do that and if you must have dmy in the front-end then you may have to resort to using set dateformat DMY to get it to work (or split and reassemble the date string within the procedure)


  7. nigelrivett New Member

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by Madhivanan</i><br /><br />Always use Universal format yyyy-mm-dd or yyyymmdd<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br />yyyy-mm-dd isn't unambiguous<br />yyyy-mm-ddThh:mm<img src='/community/emoticons/emotion-7.gif' alt=':s' />s.mmm<br />and<br />yyyymmdd<br /><br />are though.
  8. FrankKalis Moderator

    [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a><br />Ich unterstuetze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>)
  9. cmdr_skywalker New Member

    When you submit the parameter @dt_src_date, it is using the 'dd/mm/yyyy' format and it will generate an error in the where clause (auto convertion to datetime) because it is not the default format supported by SQL Server. You probably want to convert that to VARCHAR instead of DATETIME (or vice versa).

    May the Almighty God bless us all!
  10. Adriaan New Member

    IMHE, as long as you are using VARCHAR(10) for any variable in T-SQL that will hold a date, and a hyphen (-) as date separator, 'yyyy-mm-dd' is completely unambiguous.<br /><br />Since MS's thinking seems to be completely US-centric, 'mm/dd/yyyy' is also unambiguous (again IMHE).<br /><br />If you're supplying date values from a VB or similar front-end, you need to be aware that the date separator you supply will be overruled if you're using a local variable of a Date or Variant type, or using functions like Date() or Now(). You must store the date in a string type variable, and include the escape character on your format expression ("yyyy-mm-dd"). Also avoid Date type variables, since their format reflects the Windows locale settings on the workstation.<br /><br />One often overlooked fact is that the Windows locale can also have a time separator other than a colon (<img src='/community/emoticons/emotion-1.gif' alt=':)' /> - again use the escape character in the format expression.<br /><br />And finally, the "AMPM" switch for the time format should also be avoided: use the 24 hour clock.<br /><br />And now if someone would step in with some info on the Hajiri calender ...
  11. Madhivanan Moderator

    >>Since MS's thinking seems to be completely US-centric, 'mm/dd/yyyy' is also unambiguous (again IMHE).

    What happens if the date format of the server is set to dmy and the client send the date 01/02/2006?


    Failing to plan is Planning to fail
  12. Adriaan New Member

    As long as you're using a string that spells out the date, or a VARCHAR variable that holds a spelled-out date, then T-SQL will correctly interpret dates in the 'mm/dd/yyyy' and 'yyyy-mm-dd' formats regardless of Windows locale.

    The question then becomes how these strings are compiled by the client app, where again you have to avoid the influence of the Windows locale, which affects both date functions and date variables. So use character/string variables here too when storing dates - and don't forget to include code that handles blank dates (NULLs).

    Note that even T-SQL's own GETDATE() function can cause problems, for example when returning data to the client app!
  13. Madhivanan Moderator

    Well. I never used format(date,"yyyy-mm-dd"). How does it differ from yyyy-mm-dd as both returns same result?


    Failing to plan is Planning to fail
  14. Adriaan New Member

    If you don't include the "" character, the date delimiter of the Windows settings will be used. You're probably testing on a computer with "-" as the date delimiter, so the "" doesn't make a difference.

    You're testing in Access or Excel, right? Close Access or Excel. Now change the date delimiter on Windows to ".". Re-open Access or Excel and try Format(Date, "yyyy-mm-dd") and Format(Date, "yyyy-mm-dd").

    "yyyy-mm-dd" ---> 2006.05.26
    "yyyy-mm-dd" ---> 2006-05-26

    Did I already mention that MS is US-centric? You see, "mm/dd/yyyy" always comes out correctly, regardless of the date delimiter on Windows. At least, that's what the situation is right now. It certainly doesn't hurt to add a "", even for a "/".
  15. Madhivanan Moderator

    Thanks Adriaan. I learnt new one today [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail

Share This Page