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
294 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
Why You Need a DevOps Toolchain

IT needs to deliver services with more agility and velocity. IT must roll out application features and innovations faster to keep up with customer demands, which is where a DevOps toolchain steps in. View the infographic to see why you need a DevOps toolchain.

 
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

Business Impact of IT Communications

What are the business impacts of how well businesses communicate during an IT incident? Targeting, speed, and transparency all matter. Find out more in this infographic.

Question has a verified solution.

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

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

740 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