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.
Last Comment
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?
ASKER
--Old record
SELECT CONVERT(VARCHAR(10) ,cast(rtrim(ltrim(Field_Da
where temp.code = '12345'
--Today's record
SELECT CONVERT(VARCHAR(10) ,cast(rtrim(ltrim(Field_Da
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??????
SELECT CONVERT(VARCHAR(10) ,cast(rtrim(ltrim(Field_Da
where temp.code = '12345'
--Today's record
SELECT CONVERT(VARCHAR(10) ,cast(rtrim(ltrim(Field_Da
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??????
ASKER
any suggestions ???
That should be the case. Can you post the output of both your queries?
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
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.
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
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Microsoft SQL Server
Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.
171K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
