Date format Error 1

[smil3y]

I am upgrading a VB6 database application from Access to SQL Server. I am using MSDE2000 as my test engine.

I converted the Access database to mdf and log files and have been successfully completing queries until I included update or insert queries which included dates. I get a runtime error "The conversion of a char data type to a date time data type resulted in an out of range date time value"

I have completed several checks and found the following:
My application displays dates in the format dd//mm/yyyy.
All my dates in MSDE2000 are in the format dd/mm/yyyy.
If I do an insert it fails unless the 'dd' (in the application format)is less than 12. For example 1/06/2006. When I check server side (within MSDE) the date was displayed as mm/dd/yyyy or 6/01/20006.

I suspect all the dates within MSDE should be in the format mm/dd/yyyy.

Has anyone seen this type of problem before and if so how can I correct the date format. MSDE will not allow me to update the fields from my VB application.

 

[SQLBill]

When using dates that are not in ymd format, you should use SET DATEFORMAT so that SQL Server can properly interpret the date.

SET DATEFORMAT DMY
SELECT ......


SET DATEFORMAT DMY
INSERT.....

Etc.

-SQLBill

Posting advice: FAQ481-4875

 

[smil3y]

Thanks.
Do I perform this on the SQL Server. I wonder whether I can do it on MSDE2000.

 

[gmmastros]

See this thread thread183-1240616 for a complete explanation.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[smil3y]

Very good article.

I have limited SQL experience - is there any way I can change the date format for every date entry to 'yyyy/mm/dd'.

 

[gmmastros]

NO.

SQL Server's internal representation of dates is similar to VB's. Behind the scenes, it's actually a number with a fractional component to it.

For example:

Select GetDate() -- will return the current date and time
Select Convert(Decimal(18,12), GetDate()) -- will return the decimal number representation.

The good news is that you really don't have to worry about it. If you 'send' SQL server dates format in the ISO format yyyymmdd then the date will ALWAYS be interpreted correctly by SQL Server. When returning date fields FROM SQL Server, they will be actual vb dates, so again, no problem.

When displaying the dates in VB, you should be using the the format function like so:

txtDateField.Text = Format(RS("DateField"), "[!]Short Date[/!]")

By using Short Date, the date will be formatted to correspond to the users regional settings.

Click Start -> Control Panel -> Regional And Language Options
On the Regional Options tab, you can see what the [!]Short Date[/!] format is. Of course, you can change the regional settings to match your preference, and so can your users. By using Short Date, they 'should' never complain about the representation.

Bottom line:

1) Send dates to SQL Server in the ISO format yyyymmdd
2) Show the dates to the user based on their regional settings.

If you religiously follow this advice in your app, you'll never go wrong.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[smil3y]

Thanks again
What I propose now is to convert all the date fields in my access database to 'yyyymmdd' and then export the database to SQL (again) then continue my design keeping in mind your suggestions.

 

[smil3y]

Well that did not work!

First I set the format for all dates in the Access database to yyyy/mm/dd, and then I exported. The dates still displayed as dd/mm/yyyy.

Next, instead of using the upsizing wizard to export Access to SQL I even tried creating a new SQL database and then using VB to copy across table records on at a time with dates in the format yyyy/mm/dd - still displays incorrectly.

I can send dates to SQL but they always display in the format dd/mm/yyyy. Is this correct? I thought they would display as yyyy/mm/dd.

 

[gmmastros]

What is the data type for the date field in access? I hope it's actually a date field and not a text field that holds dates.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[smil3y]

The field in the original database is formated 'shortdate'. In the changes I made yesterday I formatted as yyyy/mm/dd. The dates all changed and dispalyed as I expected.
When I upsized to SQL and checked the format, the dates all displayed as dd/mm/yyyy.
Am I correct in assuming that the dates will always display (in SQL) as whatever the regional settings are (ie dd/mm/yyyy)?

 

[gmmastros]

There is no such concept as 'display in sql server'. Unlike Access, SQL Server is ONLY data. Sure, there are tools like Enterprise Manager and Query Analyzer, but both of those are application that allow you to 'see and do stuff' with the database.

If you use access to link to the SQL Server database, the display of the dates will correspond to Access's display of dates, which presumably uses regional settings.

Even within Access, you can change the format for a date field, but you are not changing the data at all, you are simply changing the way Access displays it to you (the user).

There's a subtle, but sometimes significant, distinction between the data itself and the way it is displayed.

For example, in SQL Server, you can convert a date to a varchar. During the conversion, you can specify the way the data is displayed. It's still the same data, it just in the way it is displayed that is different.

Ex.

Select Convert(VarChar(10), GetDate(), 101),
       Convert(VarChar(10), GetDate(), 103),
       Convert(VarChar(20), GetDate(), 100)

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[smil3y]

Thanks - will have another look tonight.