Solved

SQL Server 2014 Management Studio Trouble with Dates

Posted on 2016-08-03
15
63 Views
Last Modified: 2016-09-08
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
0
Comment
Question by:ScuzzyJo
  • 4
  • 4
  • 2
  • +2
15 Comments
 
LVL 26

Accepted Solution

by:
Zberteoc earned 250 total points (awarded by participants)
ID: 41740771
The import doesn't convert the dates. It takes the impot value and it validates it. If the inpiut is a valid date it will import. When you select from the database you will see the date value. The format is irrelevant as long is the same date. In SQL server there is a default format when you SELECT or PPRINT a datetime value but internally there is NO format but just a pair of 2 integer numbers, first for date part and second for the time part. The format comes into place only when you SELECT or PRINT the value in order to see the date, when you will have to convert the date time to a string representation. For that you have to use CONVERT function:
declare 
    @dte as datetime=getdate()

SELECT
    @dte as Default_Format,
    CONVERT(varchar(35), @dte, 106) as [DD Mon YYYY],
    CONVERT(varchar(35), @dte, 101) as [MM/DD/YYYY],
    CONVERT(varchar(35), @dte, 100) as [Mon D YYYY hh:mmAM/PM]

Default_Format          DD Mon YYYY                         MM/DD/YYYY                          Mon D YYYY hh:mmAM/PM
----------------------- ----------------------------------- ----------------------------------- -----------------------------------
2016-08-03 11:14:34.600 03 Aug 2016                         08/03/2016                          Aug  3 2016 11:14AM

Open in new window

As you can see the exact same date only the printout is different. You have to use CONVERT in order to change the format:

https://msdn.microsoft.com/en-ca/library/ms187928.aspx
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 41740778
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?
0
 
LVL 26

Assisted Solution

by:Nick67
Nick67 earned 125 total points (awarded by participants)
ID: 41741298
Once I've imported it, it converts to an American date, e.g. 2015-03-17.
Dates in SQL Server are fun, fun things.
http://sqlmag.com/sql-server/solving-datetime-mystery.
Your BEST assumption is that any date sent to SQL Server as a string ('1/2/2016') is going to get bolluxed up if yuo leave any ambiguity in it

Why?

Because there are ALL SORTS of factors that play into how SQL will see that date.
There's your computer's regional settings
There's the server's
There's the SQL Server language, which can be changed permanently, or per query
There's the origin of the import file's computer's regional settings
...and those are the ones off the top of my head.

So, BEST is ISO format
'YYYY-MM-DD'
That never goes wrong
Next best is medium date
'DD-MMM-YYYY'
That never goes wrong either and if you are dealing with only English, it's golden.
Convert within Excel to either of these date formats and you may be good.
Convert the date to a string and import it and that will be even better.
Dates are persnicketty.

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.
Um.
5093865 & 28/12/2012 00:00 should yield string concatenation of 50936865Dec 28 2012 12:00AM
What were you hoping for?
0
 
LVL 26

Assisted Solution

by:Chris Luttrell
Chris Luttrell earned 125 total points (awarded by participants)
ID: 41741792
A very good article to read about date/time values in SQL Server, https://www.simple-talk.com/sql/t-sql-programming/how-to-get-sql-server-dates-and-times-horribly-wrong/
According to SQL Server documentation, the database engine stores a DATETIME value as two integers. The first integer represents the day and the second integer represents the time.
And the biggest mistakes beginners make is
Assuming that SQL Server stores date/time values as formatted strings
*Article and all quotes attributed to Robert Sheldon in the Simple-Talk article
0
 

Author Comment

by:ScuzzyJo
ID: 41741885
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
0
 
LVL 16

Expert Comment

by:Gerald Connolly
ID: 41742281
Getting your combined key correct could be done like this in Excel
  =A2&TEXT(B2,"yyyy-mm-dd")
0
 

Author Comment

by:ScuzzyJo
ID: 41742431
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
0
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 
LVL 26

Expert Comment

by:Chris Luttrell
ID: 41742692
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.
0
 
LVL 26

Expert Comment

by:Nick67
ID: 41742832
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.
0
 

Author Comment

by:ScuzzyJo
ID: 41742857
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.
0
 
LVL 26

Expert Comment

by:Nick67
ID: 41742968
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.
0
 
LVL 16

Expert Comment

by:Gerald Connolly
ID: 41743550
Well its similar in SQL

From http://www.w3schools.com/sql/sql_func_format.asp
SELECT ProductName, Price, FORMAT(Now(),'YYYY-MM-DD') AS PerDate FROM Products;
0
 
LVL 26

Expert Comment

by:Nick67
ID: 41762649
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.
0
 

Author Comment

by:ScuzzyJo
ID: 41789628
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
0

Featured Post

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

706 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now