[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Incorrect Date format SQL 2005 on Windows 2008 R2

Posted on 2011-10-05
9
Medium Priority
?
376 Views
Last Modified: 2012-05-12
Hi,

I'm running a job to DTS data from SQL 2005 to SQL 2000. This is actually running across 13 servers without any problem.

The date format is change in one server since October 1st, which was working in September.

I'm using Export Wizard and exporting the data into SQL 2000. I get the date format as mm/dd/yyyy

I then save that exact Export as SSIS package (saved under the DB and file) and then schedule it to run. It runs fine but the date is formatted as dd/mm/yyyy

I'm doing a simple select  CONVERT(varchar, dbo.table1.field1, 101) AS [Date] and Inserting into a DATE field on the destination server which is smalldatetime.

Again, I'm running the exact process on 13 other servers without an issue. The only difference between this server and the other servers is the OS. Other servers are running SQL 2005 on Windows 2003 R2 64bit and this one is running on Windows 2008 R2.

Just to add that I verified the Date format on the OS level and it is set to M/dd/yy. I actually cross checked that with the other servers.

I'm not sure that how this was working fine in September and now comes October, the date format is changed.

Thanks in advance for your help.



0
Comment
Question by:ezzadin
  • 4
  • 2
  • 2
  • +1
9 Comments
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 36922341
Try sending it as format 112 or 120 instead of 101. Quite honestly, though, it looks strange to convert a DateTime column to VarChar simply to send to another DatTime or SmallDateTime column. What us the purpose of the conversion?
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 36922353
By the way, the date format on the OS is not what matters, but what language the SQL server or a connection to it is set to. Therefore, if someone set the default language to British English On server or just for the connection, then it's default date format becomes 103 or DD/mm/YYYY ... Anyway, 112 is YYYYMMDD and 120 is YYYY-MM-DD HH:MI:SS ... These formats tend to work on both US and British English systems without error.
0
 
LVL 6

Expert Comment

by:dan_mason
ID: 36923194
I agree with the above - format 112, without the varchar conversion, should do the job fine.
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36923999
>>I'm doing a simple select  xCONVERT(varchar, dbo.table1.field1, 101) AS [Date] and Inserting into a DATE field on the destination server which is smalldatetime. <<
This should then be (fix the obvious typo in xCONVERT):
xCONVERT(smalldatetime, dbo.table1.field1, 101) AS [Date]
0
 

Author Comment

by:ezzadin
ID: 36926130
The destination server needs to get the date in mm/dd/yyyy format, no hh:mm:ss  and there is a Group By on the date, that is why CONVERT(varchar, dbo.table1.field1, 101) AS [Date]  is used.

This works on other servers and also works if I use Export Wizard. it is failing when SSIS is used.

Thanks.
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 36926332
Okay, there are other ways to eliminate the time portion, but since you are familiar with CONVERT() -- use what I said which is 112 format code: CONVERT(CHAR(8), dbo.table1.field1, 112)
0
 
LVL 60

Accepted Solution

by:
Kevin Cross earned 2000 total points
ID: 36926350
To answer the question of why it fails in SSIS, the source or destination is likely set to a different default language (i.e., date format) and so it is processing based on that. Because you are converting to VARCHAR, the format matters as it has to do an implicit conversion back to SMALLDATETIME. If you strip out the time and send as a SMALLDATETIME, you probably won't get the issue: CONVERT(SMALLDATETIME, DATEDIFF(DD, 0, GETDATE()))

If you are indeed using SQL 2008, remember you have the DATE data type that you can use to make life easier also: CONVERT(DATE, GETDATE())
0
 

Author Comment

by:ezzadin
ID: 36926602
CONVERT(SMALLDATETIME, DATEDIFF(DD, 0, GETDATE())) Resolved the issue.


Thanks.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36927117
>>The destination server needs to get the date in mm/dd/yyyy format<<
As you have discovered that is never a good idea.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…

872 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