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


<div>
Hi Everyone<br />
<br />
Thanks for your comments.<br />
<br />
The reason I'm concatenating stuff is that I have two tables which are full of ledger postings. &nbsp;One shows the items which have already been matched and reconciled. &nbsp;The other table contains the new set of transactions. &nbsp;By combining the &quot;number&quot; 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. &nbsp;This should leave me with the ones I now need to reconcile.<br />
<br />
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. &nbsp;Anywhere the difference is zero can be marked as reconciled and ignored. &nbsp;The problem is that the unique reference columns look different so the join isn't picking up the values.<br />
<br />
I've attached an Excel file which shows a few lines of the downloads after I've run the queries. &nbsp;The first tab shows the data which has previously been matched. &nbsp;The second tab shows the data newly downloaded from our SAS system, which is used to create the compare data on the third tab. &nbsp;As you can see, it doesn't pick up anything from the reconciled data at all as the values are all zero.<br />
<br />
Can anyone help me find a way to do this? &nbsp;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.<br />
<br />
Thanks<br />
Sarah<br />
<a href="https://filedb.experts-exchange.com/incoming/2016/08_w32/1110023/EE_Data.xlsx" target="_blank" class="file-inline" title="Excel file showing partial download from SQL (11 KB)"><svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 512 512"><path d="M67.508 468.467c-58.005-58.013-58.016-151.92 0-209.943l225.011-225.04c44.643-44.645 117.279-44.645 161.92 0 44.743 44.749 44.753 117.186 0 161.944l-189.465 189.49c-31.41 31.413-82.518 31.412-113.926.001-31.479-31.482-31.49-82.453 0-113.944L311.51 110.491c4.687-4.687 12.286-4.687 16.972 0l16.967 16.971c4.685 4.686 4.685 12.283 0 16.969L184.983 304.917c-12.724 12.724-12.73 33.328 0 46.058 12.696 12.697 33.356 12.699 46.054-.001l189.465-189.489c25.987-25.989 25.994-68.06.001-94.056-25.931-25.934-68.119-25.932-94.049 0l-225.01 225.039c-39.249 39.252-39.258 102.795-.001 142.057 39.285 39.29 102.885 39.287 142.162-.028A739446.174 739446.174 0 0 1 439.497 238.49c4.686-4.687 12.282-4.684 16.969.004l16.967 16.971c4.685 4.686 4.689 12.279.004 16.965a755654.128 755654.128 0 0 0-195.881 195.996c-58.034 58.092-152.004 58.093-210.048.041z"/></svg>EE_Data.xlsx</a>
</div>


Excel file showing partial download from SQL

EE_Data.xlsx

<div>
Getting your combined key correct could be done like this in Excel <br />
&nbsp; =A2&amp;TEXT(B2,&quot;yyyy-mm-dd&quot;)
</div>


<div>
Thanks, Gerald, but I need to do this in SQL rather than Excel. &nbsp;The files are enormous so it just hangs Excel, even though I have quite a decent 64 bit machine.<br />
<br />
Thanks<br />
Sarah
</div>


<div>
What is the query where you are either comparing the key fields or exporting them? &nbsp;That is where this will be resolved. &nbsp;The database has your date, you just need it output in a particular format.
</div>


<div>
<i>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.</i><br />
<br />
If you have the old machine handy, I would look at the Region settings<br />
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.<br />
<br />
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.<br />
Windows thinks that Canadian and UK date formats are the same dd/mm/yyyy<br />
But customarily, Canadians use the American mm/dd/yyyy format.<br />
It play merry hell with those folks who don't take the admonition to use dd-mmm-yyyy seriously.<br />
They start typing in <b>01/01</b> in Excel, and don't get 1-Jan-16 as expected but get Jan 2001 instead.<br />
<br />
If it worked before, that's very likely where an easy fix lies.
</div>


<div>
Thanks, Nick. &nbsp;I'll take a look but I need to try to remember my password as I haven't used that machine for ages! &nbsp;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.
</div>


<div>
99 for 100 there will be a corporate set of Regional configuration, then.<br />
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.<br />
<br />
You could also just document what you presently have, and then change settings and experiment.<br />
I'd log out/in between changes.
</div>


<div>
Well its similar in SQL<br />
<br />
From <a href="http://www.w3schools.com/sql/sql_func_format.asp" rel="ugc">http://www.w3schools.com/sql/sql_func_format.asp</a>&nbsp;<br />
<i>SELECT ProductName, Price, FORMAT(Now(),'YYYY-MM-DD')<wbr /> AS PerDate FROM Products;</i>
</div>


<div>
This is one that the Asker needs to resolve and close.<br />
The Asker needs to continue to provide input about how she is resolving her issue.<br />
Possible resolutions have been offered, but the Asker must say how the issue was resolved in this case.
</div>


<div>
Hi All<br />
<br />
I got this working and then forgot to close the question - my bad!<br />
<br />
I'll close the question and award the points. &nbsp;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.<br />
<br />
Sarah
</div>


<div>
<div class="content wysiwyg-content">
Hi. &nbsp;My question relates to SQL Server 2014 Management Studio. &nbsp;I'm having trouble with dates. &nbsp;The files I'm using are imported from Excel, but were downloaded from SAS Enterprise Guide prior to this.<br />
<br />
One of the files has a column called GLPDate. &nbsp;In Excel, this shows perfectly, e.g. 17/03/2015. &nbsp;Once I've imported it, it converts to an American date, e.g. 2015-03-17. &nbsp;I've tried using SET DATEFORMAT dmy; but that has no effect. &nbsp;It's showing as a smalldatetime.<br />
<br />
How can I get this to a British format?<br />
<br />
Also, the other file has the correct format, but I need to concatenate it with another field. &nbsp;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. &nbsp;This is driving me crazy!<br />
<br />
Thanks<br />
Sarah
</div>
</div>


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

SQL Server 2014 Management Studio Trouble with Dates

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.