Different Date Format ?  
Author Message
MianGhous





PostPosted: Wed Sep 26 14:14:30 PDT 2007 Top

SQL Server Developer >> Different Date Format ?

We move the data from DevServer to TestServer.

select convert(varchar(12), convert(datetime, ExpiryDate), 6)
from [MyTable]
where ContentId = 613

On DevServer: 11 Oct 07
On TestServer: 10 Nov 07

What could it be wrong ?

Thanks,

xke

SQL Server302  
 
 
Adi





PostPosted: Wed Sep 26 14:14:30 PDT 2007 Top

SQL Server Developer >> Different Date Format ?
> We move the data from DevServer to TestServer.
>
> select convert(varchar(12), convert(datetime, ExpiryDate), 6)
> from [MyTable]
> where ContentId = 613
>
> On DevServer: 11 Oct 07
> On TestServer: 10 Nov 07
>
> What could it be wrong ?
>
> Thanks,
>
> xke

The date format is fine. The problem is that actual date on one of
the machines. Make sure that both machines have to correct date and
time.

Adi

 
 
Erland





PostPosted: Wed Sep 26 15:21:48 PDT 2007 Top

SQL Server Developer >> Different Date Format ?
> We move the data from DevServer to TestServer.
>
> select convert(varchar(12), convert(datetime, ExpiryDate), 6)
> from [MyTable]
> where ContentId = 613
>
> On DevServer: 11 Oct 07
> On TestServer: 10 Nov 07
>
> What could it be wrong ?

The database design. It appars that ExpiryDate holds a date value in some
format which is not invariant with regards to language and datetime
settings. See more here: http://www.karaszi.com/SQLServer/info_datetime.asp.


--


Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
 
 
Aaron





PostPosted: Wed Sep 26 15:22:54 PDT 2007 Top

SQL Server Developer >> Different Date Format ? What is the data type of ExpiryDate? What is the content of that column in
this case? Is one server using British language or DMY dateformat settings?




> We move the data from DevServer to TestServer.
>
> select convert(varchar(12), convert(datetime, ExpiryDate), 6)
> from [MyTable]
> where ContentId = 613
>
> On DevServer: 11 Oct 07
> On TestServer: 10 Nov 07
>
> What could it be wrong ?
>
> Thanks,
>
> xke
>

 
 
--CELKO--





PostPosted: Wed Sep 26 16:06:34 PDT 2007 Top

SQL Server Developer >> Different Date Format ? Never use strings for temp****data in SQL; we have temp****data
types in the language.

You also do not seem to know about the ISO-8601 Standards for
displaying temp****data. Don't you remember that whole Y2K thing a
few years ago??

 
 
xke





PostPosted: Thu Sep 27 06:38:24 PDT 2007 Top

SQL Server Developer >> Different Date Format ? Still not sure how to fix this issue but it looks like it wasn't
caused by sql server data transfer (even though there is a different
format date between the two servers). And the problem is only when a
custom made js calendar kicks in.
This is the scenario:
I have a js calendar on one of my pages and when the application runs
on DevServer, the javascript will set a date which is correctly saved
on the DB.
Because the formatdate is different on TestServer, the same calendar
date will save a different date.
For example running on Dev, 11 Oct 2007 will be 11 Oct 2007 but on
Test the same date will become 10 Nov 2007.
So it looks like it's a client date format.

Now the issue become not so much SQL Server related. And the question
would be if I can somehow figure out the sql server format date and
tell my js (client) calendar to render the date in the DB server
format.

If you have any idea about this pls let me know.

Thanks,
xke



> We move the data from DevServer to TestServer.
>
> select convert(varchar(12), convert(datetime, ExpiryDate), 6)
> from [MyTable]
> where ContentId = 613
>
> On DevServer: 11 Oct 07
> On TestServer: 10 Nov 07
>
> What could it be wrong ?
>
> Thanks,
>
> xke


 
 
Tibor





PostPosted: Thu Sep 27 08:54:33 PDT 2007 Top

SQL Server Developer >> Different Date Format ? > Now the issue become not so much SQL Server related. And the question
> would be if I can somehow figure out the sql server format date

There is no such thing as a "SQL Server format date". SQL Server accepts various formats for date
input, and you need to make sure that your application expresses the date in a language neutral way.
It is all in the article already suggested in this thread:
http://www.karaszi.com/SQLServer/info_datetime.asp

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi




> Still not sure how to fix this issue but it looks like it wasn't
> caused by sql server data transfer (even though there is a different
> format date between the two servers). And the problem is only when a
> custom made js calendar kicks in.
> This is the scenario:
> I have a js calendar on one of my pages and when the application runs
> on DevServer, the javascript will set a date which is correctly saved
> on the DB.
> Because the formatdate is different on TestServer, the same calendar
> date will save a different date.
> For example running on Dev, 11 Oct 2007 will be 11 Oct 2007 but on
> Test the same date will become 10 Nov 2007.
> So it looks like it's a client date format.
>
> Now the issue become not so much SQL Server related. And the question
> would be if I can somehow figure out the sql server format date and
> tell my js (client) calendar to render the date in the DB server
> format.
>
> If you have any idea about this pls let me know.
>
> Thanks,
> xke
>
>

>> We move the data from DevServer to TestServer.
>>
>> select convert(varchar(12), convert(datetime, ExpiryDate), 6)
>> from [MyTable]
>> where ContentId = 613
>>
>> On DevServer: 11 Oct 07
>> On TestServer: 10 Nov 07
>>
>> What could it be wrong ?
>>
>> Thanks,
>>
>> xke
>
>