DateTime Timestamp
-
Hi everyone Hope someone can help me cos I am becoming more and more stumped. :( Its a bit involved so please bear with me. I have a datetime column in a table. This is set via a stored proc in vb.net to be of a type Data.SqlDbType.DateTime. Now this stores the Date passed in as mm-dd-yyyy. When I read it back vb.net does a grand job of turning it into the localised(UK) format, which is dd-mm-yyy for me with no extra processing on my behalf. Problem comes in a view that I am using. This has two additional columns made up from this date using the commands.
, CAST(CONVERT(CHAR(10), dt_requested,103) as datetime) 'dt_requested_date' , CAST(CONVERT(CHAR(8), dt_requested,8) as datetime)-2 'dt_requested_time'This is mostly so that I can perform so time analysis on the fields. However this does not work as I get a convert error with the casting of the datatype because even tho vb.net is expecting to read it in as mm-dd-yyyy and not in the way it is stored as dd-mm-yyyy. The other chaps that I work with say that in vb6 they used the TimeStamp sql Data type to store it as a binary but I am unable to get that to work with vb.net as it says it needs to be converted from a byte() which as the date is being read in as a string won't work...or I can't get it to work...one of the two. Can anyone help me out? its driven me mad all morning! If you need any further info let me know. realise I prob haven't explained it too well :^) lol Thanks Ian -
Hi everyone Hope someone can help me cos I am becoming more and more stumped. :( Its a bit involved so please bear with me. I have a datetime column in a table. This is set via a stored proc in vb.net to be of a type Data.SqlDbType.DateTime. Now this stores the Date passed in as mm-dd-yyyy. When I read it back vb.net does a grand job of turning it into the localised(UK) format, which is dd-mm-yyy for me with no extra processing on my behalf. Problem comes in a view that I am using. This has two additional columns made up from this date using the commands.
, CAST(CONVERT(CHAR(10), dt_requested,103) as datetime) 'dt_requested_date' , CAST(CONVERT(CHAR(8), dt_requested,8) as datetime)-2 'dt_requested_time'This is mostly so that I can perform so time analysis on the fields. However this does not work as I get a convert error with the casting of the datatype because even tho vb.net is expecting to read it in as mm-dd-yyyy and not in the way it is stored as dd-mm-yyyy. The other chaps that I work with say that in vb6 they used the TimeStamp sql Data type to store it as a binary but I am unable to get that to work with vb.net as it says it needs to be converted from a byte() which as the date is being read in as a string won't work...or I can't get it to work...one of the two. Can anyone help me out? its driven me mad all morning! If you need any further info let me know. realise I prob haven't explained it too well :^) lol Thanks Ian -
The error could be at both, sorry that doesn't help does it. If i do the (CONVERT(CHAR(10), dt_requested,103) as datetime) using the value 103 as the type which is the british datetime(what I want)then I get a convert error at the database level of
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.when running my select on the view. If i do it using the american standard which is 101...i think... then I get the error at the app level because my app is using expecting dates to be in the british format. That make sense? The problem comes with the way the DateTime is initially automatically stored in the database as this is stored in the british format as per my localisation settings. I don't want to have to string format my dates whenever i use them so any help would be appreciated. Cheers Ian