October 1, 2002 at 8:52 am
I am fairly new to the SQL 2000 environment and i am having a difficult time trying to convert a CHAR Datatype Field to A datetime field. Can anyone lead me in the right direction with the syntax for either the Convert Or Cast function to resolve this Problem. E.g. of the data is 021002, or 010501.
Anthony Malone
Anthony Malone
October 1, 2002 at 9:01 am
Here is two examples that might help you decide how to handle this:
-- if 021002 (yymmdd) is October 2, 2002
select cast('021002' as datetime)
-- 021003 (ddmmyy) is October 2, 2003
select cast(substring('021003',5,2) + substring('021003',3,2) +
substring('021003',1,2) as datetime)
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
October 2, 2002 at 4:06 am
CAST (DATAVALUE AS DATATYPE)
or
CONVERT(CHAR,DATAVALUE,DATEOUTPUTFORMAT) (See BOL CONVERT about the values for DATEOUTPUTFORMAT)
Also keep in mind that dates can be formated on input differently and you may need to use SET DATEFORMAT to set position.
If input is mmddyyy then do SET DATEFORMAT mdy (see SQL BOL SET DATEFORMAT) (note: for user input no effect on table).
Also you are effected by your logins default language.
Also if the data was not explicitly stored as '021002' when inserted thru a query but instead was submitted as 021002 then the leading 0 may have been dropped so you may need to concatinate a 0 to the front before anything else. Use LEN and CASE to decide in your query.
Otherwise Gregs statements are right on.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply