| Author |
Topic |
|
cddot
Starting Member
37 Posts |
 Posted - 2011-01-04 : 03:30:03
|
| SELECT convert(datetime, '23AUG2010', 101) a, convert(datetime, '10/24/2010', 101) b, convert(datetime, '2010AUG25', 101) cThis runs and gives the results I want. However, the question is what the STYLE code value 101 supposed to do? It's supplied but it seems to have no effect? |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-01-04 : 03:35:33
|
| For the first and last it won't have an effect as the character date is unambiguous (for the englich language)It would indicate how the character date is to be converted to a datetime.101 means mm/dd/yyyy which is the format of your middle date - without this it might swap round the days and months.I always use yyyymmdd which is an unambiguous format - one of the only two that will always work without a style (and the only date only format).==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2011-01-04 : 03:37:36
|
| It is used to indicate that the input string should be unambigious or in the format mm/dd/yyyy. Yo will get error for the input '28/09/2010'MadhivananFailing to plan is Planning to fail |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2011-01-04 : 03:38:51
|
 MadhivananFailing to plan is Planning to fail |
|
|
|
cddot
Starting Member
37 Posts |
Posted - 2011-01-04 : 03:48:37
|
| "unambigious or in the format mm/dd/yyyy"OK. So, in other words, if it can work out un-ambiguously what the date is, the STYLE code is ignored.Only if the date is ambiguous, then it refers to the supplied STYLE value for a definitive answer. Right? |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2011-01-04 : 04:12:58
|
quote:
Originally posted by cddot
"unambigious or in the format mm/dd/yyyy"OK. So, in other words, if it can work out un-ambiguously what the date is, the STYLE code is ignored.Only if the date is ambiguous, then it refers to the supplied STYLE value for a definitive answer. Right?
Yes you are correctMadhivananFailing to plan is Planning to fail |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-01-04 : 04:38:05
|
quote:
Originally posted by cddot
"unambigious or in the format mm/dd/yyyy"OK. So, in other words, if it can work out un-ambiguously what the date is, the STYLE code is ignored.Only if the date is ambiguous, then it refers to the supplied STYLE value for a definitive answer. Right?
Not really.There are some styles that will be converted whatever the explicit style - like yyyymmdd, yyyy-mm-ddThh:mm:ss, dd mmm yyyy (this one is not so good as it is language dependent).Anything like ddmmyyyy mmddyyyy,dd/mm/yyyy will use the style to try to convert or the default if there is no style.It's worthwhile running some tests to see what the affect is.note that although yyyymmdd is ok yyyy-mm-dd can be interpreted as yyyy-dd-mm if you don't include a style or the wrong one.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
Kristen
Test
22859 Posts |
Posted - 2011-01-04 : 04:50:18
|
If you possibly can supply dates ONLY in "yyyymmdd" format.My personal view is that SQL should never have been built with all these date conversion mechanisms. SQL attempts to interpret any date that is thrown at it. "010203" can be 01-Feb-2003, 02-Jan-2005, 03-Feb-2001 (this would be the default), or even other combinations.Unfortunately because this "do my best" scenario exists a lot of testing does not uncover any errors as the data used, and the tester's environment, happens not to exercise the various implicit conversions that SQL might choose to use  . The user logs in, their user account happens to have a different Country or Language defined (or quite possibly the settings on their local PC are used to influence those SQL settings) - or the database is moved to a server that itself has different configuration - and THEN all hell breaks loose The only time it may be valid to have these sorts of conversion is on a data import stream where the data is coming in in a guaranteed format, and needs converting unambiguously to SQL's only DATETIME datatype (or DATE or TIME etc.)(On those "stream" data conversions my preference would be to use the "SET DATEFORMAT dmy" statement to control the conversion, rather than CONVERT)Data that COULD contain ANY of the examples you give should NEVER exist, please! otherwise there is no hope that you will be certain to unambiguously convert the data 100% of the time.If data is coming from your application then change you application so that it only ever comes in either the language's native DATE / DATE-TIME format (in which case the database transport layer will unambiguously convert it), or if it is coming in a string format then useyyyymmddyyyymmdd hh:mm:ss.sssformats (note that there are NO hyphens)oryyyy-mm-ddThh:mm:ss.sss(hyphens are required for this format)and SQL will UNambiguously convert these.(the milliseconds / seconds are optional) |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-01-04 : 05:26:24
|
| My view is that it should never do an implicit convert.A decimal divided by an integer should throw an error - as should setting an integer variable to a decimal value.Operations on different length/type character columns should error also (apart from concatenate).==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2011-01-04 : 05:38:28
|
quote:
Originally posted by nigelrivett
My view is that it should never do an implicit convert.A decimal divided by an integer should throw an error - as should setting an integer variable to a decimal value.Operations on different length/type character columns should error also (apart from concatenate).==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy.
If there is no implicit convertions based on the datatype precedence, I think most of the existing codes would throw errorsMadhivananFailing to plan is Planning to fail |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-01-04 : 05:56:49
|
quote:
Originally posted by madhivanan
quote:
Originally posted by nigelrivett
My view is that it should never do an implicit convert.A decimal divided by an integer should throw an error - as should setting an integer variable to a decimal value.Operations on different length/type character columns should error also (apart from concatenate).==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy.
If there is no implicit convertions based on the datatype precedence, I think most of the existing codes would throw errorsMadhivananFailing to plan is Planning to fail
And probably highlight a lot of mistakes and incorrect results.It couldn't happen now other than by an option (which I think should be available).==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2011-01-04 : 06:13:19
|
"My view is that it should never do an implicit convert"me too  "It couldn't happen now other than by an option (which I think should be available)."Repeating what I have said before, elsewhere, but a "VERBOSE" / "DEBUG" mode, or WARNING LEVEL, or a LINT tool, would do me. |
|
|
|
|
Query on CONVERT function