| Author |
Topic |
|
hog
Constraint Violating Yak Guru
284 Posts |
 Posted - 2006-03-06 : 05:11:20
|
| I am constantly having trouble converting string values to dates and am about ready to open a vein!!!!I have checked out here but am still stuck :( Can anyone help please?The date on our server is yyyy-mm-dd hh:mm:ss:mmm Users pass a date value to my proc in a string and so I need to convert to correct format. I have tried convert and cast and get an data conversion error from string!convert(datetime, @date, 121)cast(@date as dattime)Any help extremely welcome.........please |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2006-03-06 : 05:40:28
|
| "Users pass a date value to my proc in a string and so I need to convert to correct format."The Users input is it via the front end application ? You should use datetime data type as input parameter and also in the front end application.----------------------------------'KH' |
 |
|
|
hog
Constraint Violating Yak Guru
284 Posts |
Posted - 2006-03-06 : 05:50:20
|
| I have no control over the front end application only what is passed to the proc I write. The paramters come in the form of a string and I have to extract from that.I can easily extract the date, 06/03/2006, but when I try to use it I get a date conversion error?I can run this raw: select * from table where dated = '01/24/2006' and it worksIf I use select * from table where dated = '24/01/2006' I get a conversion error.If I set dateformat dmy I get no error but also no results. |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2006-03-06 : 05:54:50
|
| The date pass to your stored procedure is in which format ? DD/MM/YYYY or MM/DD/YYYY ? Whatever the format is, it must be consistent.If it is MM/DD/YYYY then use convert(datetime, inputdate, 101)if it is DD/MM/YYYY then use convert(datetime, inputdate, 103)----------------------------------'KH' |
|
|
|
hog
Constraint Violating Yak Guru
284 Posts |
Posted - 2006-03-06 : 06:51:49
|
| :) the last convert stament works a treat :)Cheers |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2006-03-06 : 07:10:01
|
| It is better to use universal format yyyy-mm-ddMadhivananFailing to plan is Planning to fail |
|
|
|
hog
Constraint Violating Yak Guru
284 Posts |
Posted - 2006-03-06 : 09:34:35
|
| not much use to the users tho.The server may store it in that format but we type 06/03/2006 rather than 2006/03/06 |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-03-06 : 10:43:52
|
| "The server may store it in that format"The server stores it in a binary format (number of days since X and, separately, number of milliseconds since midnight I think).As a general point the best way to pass a date and/or time is using a datetime datatype - the moment you use a text string other things start getting in the way - like lopping off leading zeros and the like!Kristen |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-03-06 : 12:29:59
|
I think your problem is not that dates are a pain. It's strings that are supposed to be dates, but really aren't that are a pain.If you force front end applications to pass dates to stored procedures in datetime format, you will eliminate most of your problems.Tell the developers to assign the parameters like this:Set @datetime_param = '20061207 00:00:00.000' This is SQL Servers universal datetime format that will work the same no matter what the local setting on the server is.CODO ERGO SUM |
|
|
|
hog
Constraint Violating Yak Guru
284 Posts |
Posted - 2006-03-07 : 04:50:27
|
| Dudes! Check out my second post...'I have no control over the front end application only what is passed to the proc I write. The paramters come in the form of a string and I have to extract from that.'The front end was supplied by a logistics software house and as such I have no control over what gets passed to my procs. I can create new reports to be used by the front end but this front end passses all the parameters in one string.Yes this is crap and I know it, but I cannot change it so have to work around this which results in grief with the dates. However the convert...103 works fine :) |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-03-07 : 12:00:20
|
| Dude. I think if you reread your second post, you said you did have control over what was passed to your proc:"I have no control over the front end application only what is passed to the proc I write."At least that's way I read it, dude.CODO ERGO SUM |
|
|
|
hog
Constraint Violating Yak Guru
284 Posts |
Posted - 2006-03-08 : 03:31:18
|
| Mmmm?OK let me make it clearer :)I did say 'I have no control over the front end application only what is passed to the proc I write. The paramters come in the form of a string and I have to extract from that.'The second sentence states the parameters come in the form of a string, therefore I have control over this string in as much as splicing out the bits I want.The first thing I do when I need the date if passed is to splice it out of the string and convert and store it into a datetime variable.Follow me now? :) |
|
|
|
|
Dates are a complete pain!