Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
OkayPhil
Starting Member
5 Posts |
 Posted - 2002-06-13 : 14:33:28
|
| Hi, I'm having a few date issues that are driving me crazy. I'm new to SQL server 2000 after working with SQL 6.5 for a couple years. Recently, we imported all our databases from 6.5 to 2000 and realized that our dates were no longer the same.In SQL 6.5 our date fields were smalldatetime and stored dates as January 12th, 2002. Now in SQL Server 2000 it stores the date as 12/01/2002. When I run a query and display it on my asp pages, it displays the date as 01/12/2002. It's giving me an ulcer. I've tried entering it differently, I've looked for a setting or property for date formats, but I can't find anything that works. |
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-06-13 : 14:54:53
|
| Data and time data (using the datetime type) is stored in SQL Server 2000 (and I believe it is the same in 6.5) physically as two 4-byte integer. The first 4 bytes store the number of days before or after the base date, January 1, 1900. The other 4 bytes store the time of day represented as the number of milliseconds after midnight. So what you are talking about is not on the storage layer, but rather on the presentation layer.Try ... select convert(varchar, getdate(), 107) ... if you don't like the way that looks, you can use the date functions to display a date however you like.<O> |
 |
|
|
OkayPhil
Starting Member
5 Posts |
Posted - 2002-06-13 : 15:05:35
|
| I guess my only choice is a date function. But why does it do this? It figures that it would display the value in the field as it's stored. Apparently, that would be too simple. Things seemed so simple back with 6.5 |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-06-13 : 15:23:41
|
That's not a database issue AT ALL. Your comment:quote:
It figures that it would display the value in the field as it's stored.
Is inaccurate. You're confusing display format with value.The problem is most likely the regional settings for dates for the SQL Server and ASP web servers. The SQL Server is probably set for European date format while the web server is using U.S. date format. Take a look in Books Online under SET DATEFORMAT and play around with those settings. Also look in the Date/Time control panel on both machines and check their settings. |
|
|
|
OkayPhil
Starting Member
5 Posts |
Posted - 2002-06-13 : 15:54:35
|
| Okay, here's the story....I've got SQL server and IIS running off the same box. I check in my regional settings through control panel for my date display at it's set to dd/MM/yyyy. So that's that for the machine and IIS. Like I said, i'm not familiar with SQL Server 2000, so where would I check if SQL Server is using a European date format? I tried in the properties of SQL server and the only thing dealing with date is whether or not to use 4 digit year or 2 digit year. It says nothing else about date format.BTW...thanks for the help so far, trial by fire is not always fun. |
|
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-06-13 : 15:59:25
|
Maybe you didn't hear Rob when he said . . quote:
Take a look in Books Online under SET DATEFORMAT
<O> |
|
|
|
OkayPhil
Starting Member
5 Posts |
Posted - 2002-06-13 : 16:01:16
|
| Uh....yeah....uh...of course I did. Ooops...sorry. |
|
|
|
OkayPhil
Starting Member
5 Posts |
Posted - 2002-06-13 : 16:22:27
|
| Thanks guys, I figured it out. I had checked the regional settings and I didn't know you could manually modify the date format. I saw that they were dropdown boxes and figured that they were set in stone. Once I changed them, I found that it outputs the dates exactly the same way that they appear in the database. You still have to enter the date in YYYY/MM/DD ISO format though.Thanks again for your help and patience. |
|
|
|
|
|
|
|
|
Date Problems