| Author |
Topic |
|
iordanissav
Starting Member
4 Posts |
 Posted - 2008-01-31 : 06:53:12
|
| I can't create a function which contains SET DATEFORMAT dmy (Greek dateformat). Does it only work in a SP? |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-01-31 : 07:28:13
|
If you use proper datatype as DATETIME or SMALLDATETIME you will never have these issues.
E 12°55'05.25"N 56°04'39.16" |
 |
|
|
iordanissav
Starting Member
4 Posts |
Posted - 2008-01-31 : 11:15:43
|
| What do you mean? I certainly use datetime vars. I just want to make sure that the dates typed by the users in Greek format, are understood by SQL Server in the stored procedures. I think this has to do with the connection environment. How can I set this in the connection string (or in another place?) |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-01-31 : 11:20:37
|
A date is nothing more than a sequence number starting with 0 for January 1, 1900 (Microsoft SQL Server).Today January 31, 2008 is sequence number 39476. The decimal point is number of milliseconds since midnight.If you use proper datatypes to call the function, you will never have this problem.
E 12°55'05.25"N 56°04'39.16" |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2008-02-01 : 10:19:13
|
| 1 Use proper DATETIME datatype2 When expressing dates use universal format yyyymmdd HH:MM:SSMadhivananFailing to plan is Planning to fail |
|
|
|
iordanissav
Starting Member
4 Posts |
Posted - 2008-02-01 : 16:36:53
|
| I understand all of these, but I can't expect users to type a date in a yyyy-mm-dd format. Not even in mm-dd-yyyy format. 11/5/2007 is different than 5/11/2007 but both are correct for SQL server. If I could use [SET DATEFORMAT dmy] then a date entered as 11/5/2007 would actually be May 11, 2007 for SQL Server.And another thing. Can I use IsDate function in a UDF? |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2008-02-01 : 16:39:47
|
| what we're saying is that you should use datetime parameters in your app. don't generate sql like this:string sql = "select * from myTable where myDateColumn = '" + txtMyDateTime.Text + "'"date time formatting doesn't have any place in sql server._______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenpSSMS Add-in that does a few things: www.ssmstoolspack.com <- new version out |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2008-02-01 : 17:15:13
|
| I hope you are not using a call to SQL Server to validate user date input.I can't imagine any modern language that would not have to ability to validate dates.CODO ERGO SUM |
|
|
|
dashingsidds_868
Starting Member
1 Post |
Posted - 2009-09-17 : 01:26:35
|
| Hi,The date format cannot be changed in the function as time dependent activities cannot be performed. It gives the following error while creating or altering the function. "Invalid use of side-effecting or time-dependent operator in 'SET COMMAND' within a function."But the date format can be changed in the SP in which you are calling the function and the same format (as mentioned in the SP) will be taken in the function.Regards,Dhaval Samaranayeke |
|
|
|
ashish908
Starting Member
18 Posts |
Posted - 2009-11-27 : 09:58:21
|
| In my case it did not propogate to the functioni had my "Set Dateformat ..." command in the stored procedurebut my function threw the error when it referred to a date that is in conflict with the regional settingsRegards, |
|
|
|
jlgervais
Starting Member
3 Posts |
Posted - 2011-07-07 : 15:10:11
|
| i understand your problem, personally, i receive all kinds of date format in many forms of input, (i still receive paper data !!!), so i basically worked around using a small test and updateSET DATEFORMAT DMYUPDATE table SET DateVar= CONVERT(DATETIME,DateVar) WHERE ISDATE(DateVar) = 1SET DATEFORMAT YMDUPDATE RI_IMP_UCANNA SET DateVar = CONVERT(DATETIME,DateVar) WHERE ISDATE(DateVar) = 1this way, i end up with proper datetime values, then i can work with it but you can't use a SET command within a function |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
|
"set dateformat dmy" in a function