SQL Server 2014 Management Studio Trouble with Dates
Hi. My question relates to SQL Server 2014 Management Studio. I'm having trouble with dates. The files I'm using are imported from Excel, but were downloaded from SAS Enterprise Guide prior to this.
One of the files has a column called GLPDate. In Excel, this shows perfectly, e.g. 17/03/2015. Once I've imported it, it converts to an American date, e.g. 2015-03-17. I've tried using SET DATEFORMAT dmy; but that has no effect. It's showing as a smalldatetime.
How can I get this to a British format?
Also, the other file has the correct format, but I need to concatenate it with another field. When I do so, it goes from, e.g. 5093865 (1st column which is not a date) and 28/12/2012 00:00 (2nd column, which is a datetime) to 50936865Dec 28 2012 12:00AM. This is driving me crazy!
Thanks
Sarah
One of the files has a column called GLPDate. In Excel, this shows perfectly, e.g. 17/03/2015. Once I've imported it, it converts to an American date, e.g. 2015-03-17. I've tried using SET DATEFORMAT dmy; but that has no effect. It's showing as a smalldatetime.
How can I get this to a British format?
Also, the other file has the correct format, but I need to concatenate it with another field. When I do so, it goes from, e.g. 5093865 (1st column which is not a date) and 28/12/2012 00:00 (2nd column, which is a datetime) to 50936865Dec 28 2012 12:00AM. This is driving me crazy!
Thanks
Sarah
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Zberteoc
In regards to the second problem, why do you have to concatenate a number/string of digits with a date? It is normal what you see as result. What exactly do you expect/want?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi Everyone
Thanks for your comments.
The reason I'm concatenating stuff is that I have two tables which are full of ledger postings. One shows the items which have already been matched and reconciled. The other table contains the new set of transactions. By combining the "number" in the first column, which is a transaction ID, with the date, I can create a unique reference and can match them off and mark off the ones which have already been reconciled with a reconciliation date. This should leave me with the ones I now need to reconcile.
Very roughly, I've created the unique reference column in both tables, selected distinct all of one table into a comparison table and then joined the tables to match up the amounts. Anywhere the difference is zero can be marked as reconciled and ignored. The problem is that the unique reference columns look different so the join isn't picking up the values.
I've attached an Excel file which shows a few lines of the downloads after I've run the queries. The first tab shows the data which has previously been matched. The second tab shows the data newly downloaded from our SAS system, which is used to create the compare data on the third tab. As you can see, it doesn't pick up anything from the reconciled data at all as the values are all zero.
Can anyone help me find a way to do this? Oddly, this used to work fine before I got a new laptop and a new version of SQL, but I doubt that that's really a factor.
Thanks
Sarah
EE_Data.xlsx
Thanks for your comments.
The reason I'm concatenating stuff is that I have two tables which are full of ledger postings. One shows the items which have already been matched and reconciled. The other table contains the new set of transactions. By combining the "number" in the first column, which is a transaction ID, with the date, I can create a unique reference and can match them off and mark off the ones which have already been reconciled with a reconciliation date. This should leave me with the ones I now need to reconcile.
Very roughly, I've created the unique reference column in both tables, selected distinct all of one table into a comparison table and then joined the tables to match up the amounts. Anywhere the difference is zero can be marked as reconciled and ignored. The problem is that the unique reference columns look different so the join isn't picking up the values.
I've attached an Excel file which shows a few lines of the downloads after I've run the queries. The first tab shows the data which has previously been matched. The second tab shows the data newly downloaded from our SAS system, which is used to create the compare data on the third tab. As you can see, it doesn't pick up anything from the reconciled data at all as the values are all zero.
Can anyone help me find a way to do this? Oddly, this used to work fine before I got a new laptop and a new version of SQL, but I doubt that that's really a factor.
Thanks
Sarah
EE_Data.xlsx
Getting your combined key correct could be done like this in Excel
=A2&TEXT(B2,"yyyy-mm-dd")
=A2&TEXT(B2,"yyyy-mm-dd")
ASKER
Thanks, Gerald, but I need to do this in SQL rather than Excel. The files are enormous so it just hangs Excel, even though I have quite a decent 64 bit machine.
Thanks
Sarah
Thanks
Sarah
What is the query where you are either comparing the key fields or exporting them? That is where this will be resolved. The database has your date, you just need it output in a particular format.
Oddly, this used to work fine before I got a new laptop and a new version of SQL, but I doubt that that's really a factor.
If you have the old machine handy, I would look at the Region settings
I'll give you dollars to donuts that the old machine had some settings as the UK and the new one has it as the US.
I'll get the phone call right away if I forget to set the formats to US, and the machine has Canada as the location and Canada as the formats.
Windows thinks that Canadian and UK date formats are the same dd/mm/yyyy
But customarily, Canadians use the American mm/dd/yyyy format.
It play merry hell with those folks who don't take the admonition to use dd-mmm-yyyy seriously.
They start typing in 01/01 in Excel, and don't get 1-Jan-16 as expected but get Jan 2001 instead.
If it worked before, that's very likely where an easy fix lies.
If you have the old machine handy, I would look at the Region settings
I'll give you dollars to donuts that the old machine had some settings as the UK and the new one has it as the US.
I'll get the phone call right away if I forget to set the formats to US, and the machine has Canada as the location and Canada as the formats.
Windows thinks that Canadian and UK date formats are the same dd/mm/yyyy
But customarily, Canadians use the American mm/dd/yyyy format.
It play merry hell with those folks who don't take the admonition to use dd-mmm-yyyy seriously.
They start typing in 01/01 in Excel, and don't get 1-Jan-16 as expected but get Jan 2001 instead.
If it worked before, that's very likely where an easy fix lies.
ASKER
Thanks, Nick. I'll take a look but I need to try to remember my password as I haven't used that machine for ages! I might have to talk to IT at work to be able to get into it, which is a problem as I have to work at home due to an illness.
99 for 100 there will be a corporate set of Regional configuration, then.
You may be able to just ask IT over the phone for what settings should be in the Region (or regional settings) control panel applet.
You could also just document what you presently have, and then change settings and experiment.
I'd log out/in between changes.
You may be able to just ask IT over the phone for what settings should be in the Region (or regional settings) control panel applet.
You could also just document what you presently have, and then change settings and experiment.
I'd log out/in between changes.
Well its similar in SQL
From http://www.w3schools.com/sql/sql_func_format.asp
SELECT ProductName, Price, FORMAT(Now(),'YYYY-MM-DD')
From http://www.w3schools.com/sql/sql_func_format.asp
SELECT ProductName, Price, FORMAT(Now(),'YYYY-MM-DD')
This is one that the Asker needs to resolve and close.
The Asker needs to continue to provide input about how she is resolving her issue.
Possible resolutions have been offered, but the Asker must say how the issue was resolved in this case.
The Asker needs to continue to provide input about how she is resolving her issue.
Possible resolutions have been offered, but the Asker must say how the issue was resolved in this case.
ASKER
Hi All
I got this working and then forgot to close the question - my bad!
I'll close the question and award the points. I think Zbertoec actually deserves the lion's share for explaining how sql holds dates, but thanks to you all of you for your contributions.
Sarah
I got this working and then forgot to close the question - my bad!
I'll close the question and award the points. I think Zbertoec actually deserves the lion's share for explaining how sql holds dates, but thanks to you all of you for your contributions.
Sarah