VarChar data type conversion to DateTime in Stored Procedure 2

[manuela]

We've had to specify a database date field as a VarChar as product will be used internationally with varying date formats. However, need to use this field with Date / Time functions. I'd like to be able to create another field in a stored procedure which changes this VarChar to a proper DateTime field. Does anyone know which function would solve the problem?
Many thanks
Manuela

 

[rajeevnandanmishra]

Hi manuela,
I want to know suppose at one location your date format is dd/mm/yyyy (dmy)and on other location it is mm/dd/yyyy (mdy). Now there are two cases.
So for today, what will be the value in your varchar variable?
Case 1) it is '28/06/2001' and '06/28/2001' respectively.

Case 2) it is '28/06/2001' at both location.

If it is case 1
then you must had issued the command
set dateformat dmy and set dateformat mdy in your project respectively. And then you can simply convert the varchar variable using convert function like
set @myDateTimeVar=CONVERT(datetime,myVarcharvar)
if you had not issued the command then please issue one.

Hmm... if it is case 2 then you have to write a procedure which will break the varchar to a required format and then convert it to datetime.

if is case 2, please ask again if you require any help.

 

[tlbroadbent]

There is no need to write stored procedures or functions to convert to datetime format. CAST and CONVERT will do convert character columns to datetime data type columns.

Select DateCol, CAST(DateCol As datetime) From tbl
Select DateCol, CONVERT(datetime, DateCol, 101) From tbl

The 101 in the CONVERT statement indicates a style number. If you stored the dates in mm/dd/yyyy format you could use 101 to make sure the conversion worked properly for dates where ambiguity might exist (5/2/1989). The styles are listed in SQL BOL.

----------------------------------------

Can you explain why varchar was chosen to store dates rather than the datetime data type? Doesn't seem logical to me. Storing dates in character columns introduces a number of problems I wouldn't want to bother with. Terry

http://members.home.net/tlbroadbent/sqlarticles.htm

"I shall try to correct errors when shown to be errors, and I shall adopt new views so fast as they shall appear to be true views." - Abraham Lincoln

 

[manuela]

Rajeev
hope you are well, many thanks for your help. we expect Case 1 to be the norm although we may find that the dd/mm/yyyy format will be most popular, will try it out shortly

Terry
many thanks for your reply. sorry, I don't think my explanation was clear. the field has a VarChar datatype, even though it has a date value, as the product will be used internationally where different date formats may be encountered. The stored procedure is being used to do more than just enable us to convert a field's data type. will try it out shortly. many thanks,

 

[rajeevnandanmishra]

Hi manuela,
With respect to CASE 2 (and obviously CASE 1) If you had stored the date setting in one of you configuration_mst table, you can do very easily, irrespective of the date settings.
Possible values you should store are:
dttype description - selected
------- ------------ --------
DMY - dd/mm/yyyy - 0
MDY - mm/dd/yyyy - 1
YMD - yyyy/mm/dd - 0
YDM - yyyy/dd/mm - 0
ofcourse, only one row will have selected 1.

In the starting of your project you can execute a procedure up_setDate which is as follows.
CREATE PROCEDURE up_setdate as
DECLARE @SQL VARCHAR(10)
SELECT @SQL='SET DATEFORMAT '+dttype
FROM configuration_mst
WHERE selected=1
EXEC(@SQL)
RETURN

Once this procedure is executed in a session, You can simply use the statement
SELECT @myDatetimeVar=CONVERT(DATETIME,@myVarchar)
And this will convert the varchar to datetime without any ambiguity.

 

[manuela]

Rajeev
Thanks very much as always.
Regards, Manuela

 

[manuela]

Rajeev
Thought I would just use CAST and CONVERT in the SELECT statement as the stored procedure just has a series of IF statements without any variables. The Crystal Report that uses the stored procedure comes back with error message "ODBC MS SQL SERVER ERROR: conversion of a char data type to a datetime data type resulted in an out of range datetime value" !!! I have no idea why!!!
your thoughts would be gratefully received.

CAST(DateOfAudit AS DateTime)
CONVERT(DateTime, DateOfAudit, 101)

many thanks
manuela

 

[rajeevnandanmishra]

Hi manuela,

First of all it is required to identify that, in which format the data of DateOfAudit column is stored (e.g DMY or MDY or YMD). Once it is identified you should run the SET DATEFORMAT DMY (or MDY or YMD).
After that, you simply have to say
CONVERT(DateTime,DateOfAudit) and it will do the conversion.

If you still face problem, don't hesitate! You are most welcome.

 

[tlbroadbent]

As rajeevnandanmishra explained, you need to account for the various date formats. If you used CONVERT(DateTime, DateOfAudit, 101) and dateAudit has a format different from style 101 (mm/dd/yyyy) you will get that exact error. You must match the style to the format to your data. If all the rows in the database have the same format, then you just need to find the right style number. Look in SQL BOL under the Cast and Convert topic for style definitions.

If you have varying formats you may have difficulty getting a clean conversion. If you can identify the format by country or some other code, you can still use a select query and convert but the query will be a little more complex. If you cannot identify the format, then the query becomes even more complex but should be doable. Terry

http://members.home.net/tlbroadbent/sqlarticles.htm

"I shall try to correct errors when shown to be errors, and I shall adopt new views so fast as they shall appear to be true views." - Abraham Lincoln

 

[manuela]

Rajeev & Terry
Many thanks for your help and patience! all working OK now. have a good rest of day!
Manuela