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
282 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 40

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
MS SQL + Insert Into Table - If Doesnt Exist 9 36
Return 0 on SQL count 24 30
VB.NET 2008 - SQL Timeout 9 25
Alternative of IN Clause in SQL Server 3 21
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
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…
Viewers will learn how the fundamental information of how to create a table.

821 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