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

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 129
  • Last Modified:

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
0
ScuzzyJo
Asked:
ScuzzyJo
  • 4
  • 4
  • 2
  • +2
3 Solutions
 
ZberteocCommented:
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
 
ZberteocCommented:
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
 
Nick67Commented:
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
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
Chris LuttrellSenior Database ArchitectCommented:
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
 
ScuzzyJoAuthor Commented:
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
 
Gerald ConnollyCommented:
Getting your combined key correct could be done like this in Excel
  =A2&TEXT(B2,"yyyy-mm-dd")
0
 
ScuzzyJoAuthor Commented:
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
 
Chris LuttrellSenior Database ArchitectCommented:
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
 
Nick67Commented:
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
 
ScuzzyJoAuthor Commented:
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
 
Nick67Commented:
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
 
Gerald ConnollyCommented:
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
 
Nick67Commented:
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
 
ScuzzyJoAuthor Commented:
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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

  • 4
  • 4
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now