| Author |
Topic |
|
scottishcalvin
Starting Member
10 Posts |
 Posted - 2010-07-07 : 06:49:33
|
| Hi,I've got a column that has dates stored in it as the Excel format, ie 40366 for July 7th 2010.I was wanting to pull the data out in a query split into year and month but the usual Select .... Month(Table.Column) method doesn't work because it's not a date I'm using. I've played around with a Convert() function inside but I can't get it to work. Even though the 40366 style thing is a specific Excel date representation is it possible to get get the month from it?If not is there a formula to do the conversion? I thought ofSQL_String = SQL_String & "1900+FLOOR(THETABLE.CASEDATE / 365.25) AS THE_YEAR, "to get the year |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2010-07-07 : 06:59:27
|
[code]SELECT DATEADD(DAY, 40366, '18991230'), DATEPART(MONTH, DATEADD(DAY, 40366, '18991230'))[/code]
N 56°04'39.26"E 12°55'05.63" |
 |
|
|
sql-programmers
Posting Yak Master
190 Posts |
Posted - 2010-07-07 : 07:16:19
|
| Try this,select dateadd(day,-1,40366)SQL Server Programmers and Consultantshttp://www.sql-programmers.com/ |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2010-07-07 : 07:20:38
|
quote:
Originally posted by sql-programmers
select dateadd(day,-1,40366)SQL Server Programmers and Consultants
Are you sure? It's not even the correct date.
N 56°04'39.26"E 12°55'05.63" |
|
|
|
scottishcalvin
Starting Member
10 Posts |
Posted - 2010-07-07 : 09:05:18
|
| I put inDATEADD(DAY, THETABLE.THECOLUMN, '18991230')but got "SQL0206 - Column DAY not in specified tables"Then I tries putting day as 'day' (with the apostrophes) and got"SQL0204 - DATEADD in *LIBL type *N not found"Is this one of these cases where there's a different syntax/function for different systems? The driver is "iSeries Access ODBC Driver" if that helps. Sorry, I'm more of an Excel whiz, although I do want to learn a lot more about SQL and working with databases. Thanks for the fast replies and help here. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2010-07-07 : 14:33:13
|
What client tool are you using?Are you querying Microsoft Access?
N 56°04'39.26"E 12°55'05.63" |
|
|
|
scottishcalvin
Starting Member
10 Posts |
Posted - 2010-07-08 : 07:49:22
|
| It's a 400 Server my data is stored on, The driver is "iSeries Access ODBC Driver".I'm compiling a query in an Excel VBA module and then callingWith ActiveSheet.QueryTables.Add(Connection:="ODBC;DSN=MUTUAL;", Destination:=Range("A1")) .CommandText = TheString |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2010-07-08 : 08:07:17
|
And how does "theString" look like?
N 56°04'39.26"E 12°55'05.63" |
|
|
|
scottishcalvin
Starting Member
10 Posts |
Posted - 2010-07-08 : 11:22:51
|
| That's just a plain old text string compiled in a seperate code module before being passed:Dim SQL_String As String SQL_String = "SELECT DISTINCT THETABLE.GROUP, " SQL_String = SQL_String & "THETABLE.THYR, " SQL_String = SQL_String & "THETABLE.REF, " .... and so onSQL_String = SQL_String & Chr(13) & Chr(10)Not the most efficient way to compile it (continually rebuilding the string variable) but it makes it simple to edit onscreen. The existing code it it was fine up until this point |
|
|
|
scottishcalvin
Starting Member
10 Posts |
Posted - 2010-09-08 : 06:00:23
|
| In case anyone reads this doing a forum search with the same problem, I eventually worked it out using MONTH(DATE(693596-1+THETABLE.CLSDT-1))The 693596 is treated as Jan 1st 1900 by the server and the CLSDT is the Excel style of long number that is a date. So, if it was equal to 370, you'd have Jan 1st 1900, minus one day and add on 370 days = Jan 4th 1901. However, Excel mistakenly treats 1900 as a leap year (it wasn't) so you need to subtract one more. Obviously in your code you can just do -2 rather than -1-1 but it makes more sense to write it out here. |
|
|
|
|
Extract the month for an Excel date