[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
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
Medium Priority
?
306 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
Independent Software Vendors: 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 668 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 1332 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 1332 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

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

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

In this article, we’ll look at how to deploy ProxySQL.
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

656 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