DateTime Error | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

DateTime Error

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]
GO [email protected]_value int [email protected]_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 GO

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
Hi ya, have you tried calling set dateformat dmy before the procedure call? Cheers
Twan
Don’t use any setting-aware format for the very same problems you’re facing now. There are two date styles which are considered to be safe. That is style 112 and 126. You can read about them in BOL under CAST and CONVERT or here:http://www.karaszi.com/SQLServer/info_datetime.asp
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs
Ich unterstuetze PASS Deutschland e.V. http://www.sqlpass.de)
Thank you, I shall read that article now. Regards
Toni Chaffin
aka Toni
Always use Universal format yyyy-mm-dd or yyyymmdd Madhivanan Failing to plan is Planning to fail
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) Cheers
Twan
<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.
[<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>)
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!
www.empoweredinformation.com
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 …
>>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? Madhivanan Failing to plan is Planning to fail
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!
Well. I never used format(date,"yyyy-mm-dd"). How does it differ from yyyy-mm-dd as both returns same result? Madhivanan Failing to plan is Planning to fail
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 "/".
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
]]>