Solved

date format is changed from dd/mm/yyyy to mm/dd/yyyy for new records but older records show correct format with formatdate function

Posted on 2011-02-14
8
298 Views
Last Modified: 2012-05-11


Hi,

I am storing date as varchar in database table and while displaying it changing it to date format. All was working fine till this morning when we restored a backup copy of database and now while using dateformat with 103 its showing dd/mm/yyyy for all the records which were created before today and today's records are showing results as mm/dd/yyyy while using the dateformat; while date in table is stored in varchar format. Dont have a clue why its doing it. Can you please help to figure out the issue. Version of sql server is sql server 2000.  

Thanks.
0
Comment
Question by:ezkhan
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 41

Expert Comment

by:Sharath
ID: 34891218
Can you post your code? Are you using two diferent formats (One for past dates and one for present date) in your coding?
0
 

Author Comment

by:ezkhan
ID: 34891558
--Old record
SELECT CONVERT(VARCHAR(10) ,cast(rtrim(ltrim(Field_Date)) as datetime), 103) from #tempTable  as tmp (nolock)
where temp.code = '12345'

--Today's record
SELECT CONVERT(VARCHAR(10) ,cast(rtrim(ltrim(Field_Date)) as datetime), 103) from #tempTable  as tmp (nolock)
where temp.code = '23456'


for older records its displaying correct output dd/mm/yyyy but the records which were added today its displaying mm/dd/yyyy

Any thoughts??????
0
 

Author Comment

by:ezkhan
ID: 34892240
any suggestions ???
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 41

Expert Comment

by:Sharath
ID: 34892247
That should be the case. Can you post the output of both your queries?
0
 
LVL 2

Accepted Solution

by:
MeDude21 earned 167 total points
ID: 34892279
What is it actually saving to the DB? It could be that the Field_Date value is being saved incorrectly. This could be because of a change to the regional settings on the server. Do a select on the records and have a look at the raw data. If the raw data is not correct then there is a problem with the way you are inserting it into the table.
0
 

Author Comment

by:ezkhan
ID: 34894836
records are being inserted correctly;

for example;

old record for 4th of Jan 2011

in database saved as 01/09/2011

new record for 9th of fab 2011

in database saved as 02/09/2011


Raw data in database is same but output is different. Whats the role of regional settings can you please tell how to check the impact of regional settings on this output or any other thoughts.


Thanks.
0
 
LVL 18

Assisted Solution

by:deighton
deighton earned 333 total points
ID: 34895242
I notice you are querying a temp table there, where did the temp table get its data from?  Are you sure the underlying table contains data in form '01/09/2011' and '02/09/2011'?

I imagine the table feeding the temp table some how have the date formats interchanged.
0
 
LVL 18

Assisted Solution

by:deighton
deighton earned 333 total points
ID: 34895886
what does

SELECT Field_Date, CONVERT(VARCHAR(10) ,cast(rtrim(ltrim(Field_Date)) as datetime), 103) from #tempTable  as tmp (nolock)
where temp.code = '12345'


give you?  As compared to similar for the other query.
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question