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
274 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
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 40

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
 
LVL 40

Expert Comment

by:Sharath
ID: 34892247
That should be the case. Can you post the output of both your queries?
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
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

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
SQL query to summarize items per month 5 28
SSRS Matrix Grand Total 2 19
Caste datetime 2 25
SQL Server 2012 Express to Full 5 26
Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

743 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now