asked on

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

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.

Sharath S

Can you post your code? Are you using two diferent formats (One for past dates and one for present date) in your coding?

ezkhan

ASKER

--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??????

ezkhan

ASKER

any suggestions ???

Sharath S

That should be the case. Can you post the output of both your queries?

ASKER CERTIFIED SOLUTION

MeDude21

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ezkhan

ASKER

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.

SOLUTION

deighton

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

deighton

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial