dd/mm/yy instead of mm/dd/yy 1

[Sashanan]

My database has several thousand records with date values formatted as dd/mm/yy. Unfortunately SQL Server 7 refuses to recognize these as dates, as it apparently insists on the format mm/dd/yy for both datetime and smalldatetime data types.

I've fixed the problem temporarily by setting the date fields to varchar, but this causes problems when attempting to order the data by these fields - they are sorted as text rather than chronologically.

I'd like to convince SQL Server to accept dd/mm/yy as a valid way to store dates. I'd prefer not to convert to mm/dd/yy if at all possible. How could I accomplish this? I've tried using SET DATAFORMAT as advised by Books Online but I've been unable to get any results with that so far.

Thanks in advance!
"Much that I bound, I could not free. Much that I freed returned to me."
(Lee Wilson Dodd)

 

[rajeevnandanmishra]

Hi Sashanan,
First i would suggest you to create a table with same structure (but having the datetime type column, instead of varchar)
Then use the following type of statement to insert data from the original table.
INSERT myNewTable
SELECT col1, ..., CONVERT(DATETIME,myVarCharColumnName,103)
FROM myOldTable
This will convert your varchar values to datetime. And don't be confused by seeing the date data in sql. Because SQL internally stores date in different Format.

Now make myNewTable as your original table.
And for any future READ/WRITE use the following syntax:
While Reading, always use convert(char(10),myDateCol,103)
While writing :In your frontend application in the starting put the command SET DATEFORMAT DMY. This statement is session specific. So, it will be in effect from that session only. After that whenever you write, just pass your date values in the format dd/mm/yyyy and they will be stored correctly.


If you require any further clarification, just ask.


These are a few links, you may try:
thread183-108945
thread183-99681

 

[Sashanan]

Getting close to the solution now, but I seem to have another complication. The dates in the table are not stored as dd/mm/yy, but rather as dd-mm-yy. This appears to confuse the CONVERT option, which gives me a 'Syntax error converting datetime from character string'.

If I understand the cause of this error correctly, the problem could be solved if I could change the values in the column from dd-mm-yy to dd/mm/yy. Is there an easy way to do this with an UPDATE query?
"Much that I bound, I could not free. Much that I freed returned to me."
(Lee Wilson Dodd)

 

[rajeevnandanmishra]

Hi Sashanan,
Try to use:
UPDATE myTable
SET myDateColumn=REPLACE(myDateColumn,'-','/')

 

[Sashanan]

The UPDATE query worked fine, thanks. Trying to use the INSERT statement now:

INSERT myNewTable
SELECT col1, ..., CONVERT(DATETIME,myVarCharColumnName,103)
FROM myOldTable

Unfortunately this returns:

"Column name or number of supplied values does not match table definition."

I've checked and double-checked and I am positive the new table I have created is an exact duplicate of the old one, having the same columns, datatypes, lengths, nullables, default values and table owner. The only difference is that in the new table, my date field is specified as datetime instead of varchar.

What am I doing wrong here?
"Much that I bound, I could not free. Much that I freed returned to me."
(Lee Wilson Dodd)

 

[rajeevnandanmishra]

Hi Sashanan,
The error show that you have less no. of columns selected in the SELECT statement. Just check it once again.
If still face error, please post the SQL statement, structure of new table and the error.

 

[Sashanan]

Oh wait. I think I figured that one out myself. In the part

SELECT col1, col2, ...


I mentioned my date field as well, then mentioned it again in the CONVERT part. That apparently caused the error, as I now mentioned it twice.

I've managed the initial step of converting to a new table now, will let you know if I run into any other problems or if and when I succeed.

"Much that I bound, I could not free. Much that I freed returned to me."
(Lee Wilson Dodd)

 

[Sashanan]

New table completed, old table dropped, new table renamed to old table's name and SET DATEFORMAT DMY command added to my frontend VB6 application. Ordering records by date now works as it should. The customer is happy, and so am I.

Thanks a lot for your help, Rajeev! I owe you one.
"Much that I bound, I could not free. Much that I freed returned to me."
(Lee Wilson Dodd)