ACCESS FEED: RE: [MS_AccessPros] RE: pass through queries converting dates

Senin, 25 April 2016

RE: [MS_AccessPros] RE: pass through queries converting dates

Liz,

I assume you still need an expression to convert from Julian using functions available in SQL 2008?

Duane Hookom, MVP

MS Access

To: MS_Access_Professionals@yahoogroups.com
From: MS_Access_Professionals@yahoogroups.com
Date: Mon, 25 Apr 2016 16:56:01 +0000
Subject: RE: [MS_AccessPros] RE: pass through queries converting dates

Ohhhhhhh.. That makes sense. Weird. What a valuable thing to know! Cool stuff. I need to get a book to get at the other system variables.

I was indeed running 2008. I pulled up the server management tools from a 2014 folder, but it says sql server 2008.

Interesting. Thanks so much for the good lesson Duane.

From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: Monday, April 25, 2016 9:35 AM
To: Access Professionals Yahoo Group
Subject: RE: [MS_AccessPros] RE: pass through queries converting dates

Liz,

Your earlier reply "'DateFromParts' is not a recognized built-in function name. (#195)" suggests you are using an earlier version of SQL Server. That is why I asked for confirmation by running a Pass-Through :

SELECT @@Version

Duane Hookom, MVP

MS Access

To: MS_Access_Professionals@yahoogroups.com
From: MS_Access_Professionals@yahoogroups.com
Date: Mon, 25 Apr 2016 15:02:48 +0000
Subject: RE: [MS_AccessPros] RE: pass through queries converting dates

Oh. Thought I answered that. 2014. Thanks.

From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: Friday, April 22, 2016 2:32 PM
To: Access Professionals Yahoo Group
Subject: RE: [MS_AccessPros] RE: pass through queries converting dates

Again, what version of SQL Server are you using?

Try a pass-through of:

SELECT @@Version

Duane Hookom, MVP

MS Access

To: MS_Access_Professionals@yahoogroups.com
From: MS_Access_Professionals@yahoogroups.com
Date: Fri, 22 Apr 2016 20:43:34 +0000
Subject: RE: [MS_AccessPros] RE: pass through queries converting dates

Ah bummer. I am getting ODBC call failed – 'DateFromParts' is not a recognized built-in function name. (#195)

From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: Friday, April 22, 2016 1:17 PM
To: Access Professionals Yahoo Group
Subject: RE: [MS_AccessPros] RE: pass through queries converting dates

DateFromParts() is a function available in SQL Server 2012 and later.

Duane Hookom, MVP

MS Access

To: MS_Access_Professionals@yahoogroups.com
From: MS_Access_Professionals@yahoogroups.com
Date: Fri, 22 Apr 2016 20:14:19 +0000
Subject: RE: [MS_AccessPros] RE: pass through queries converting dates

Thanks.

What does the DateFromParts refer to?

From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: Friday, April 22, 2016 12:28 PM
To: Access Professionals Yahoo Group
Subject: RE: [MS_AccessPros] RE: pass through queries converting dates

Can you explain where the leftmost 1 comes from?

If the Julian date was 06113 then you could use

Dateadd(d,JulDate % 1000 - 1,DateFromParts(2000 + JulDate / 1000,1,1))

You could try:

Dateadd(d,JulDate % 1000 - 1,DateFromParts(1900 + JulDate / 1000,1,1))

I'm not sure how this would handle other dates/centuries so make sure you test it.

Duane

To: MS_Access_Professionals@yahoogroups.com
From: MS_Access_Professionals@yahoogroups.com
Date: Fri, 22 Apr 2016 19:09:00 +0000
Subject: RE: [MS_AccessPros] RE: pass through queries converting dates

Todays date would be 116113.

It's a number.

2014.

From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: Friday, April 22, 2016 11:31 AM
To: Access Professionals Yahoo Group
Subject: RE: [MS_AccessPros] RE: pass through queries converting dates

I don't believe SQL Server has a function like that. Would today's date be 06113?

Is your fieldname a number or text?

What version of SQL Server are you using?

Duane Hookom, MVP
MS Access

To: MS_Access_Professionals@yahoogroups.com
From: MS_Access_Professionals@yahoogroups.com
Date: Fri, 22 Apr 2016 18:14:37 +0000
Subject: [MS_AccessPros] RE: pass through queries converting dates

Correction Date(fieldname,CYYDDD) ;-)

From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: Friday, April 22, 2016 11:10 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] pass through queries converting dates

Pros, is there a function I'm missing to convert a date in a passthrough query?

I'm dealing with JDE dates in the format CYYDDD and have written a function to translate it, but I think if the SQL Server could handle it I could gain considerable speed.

I'm trying Date(fieldname,CYDDD) but that gives me an odbc error.

Respectfully,

Liz Ravenwood

Programmer / Analyst

B/E Aerospace | Super First Class Environments

1851 S Pantano Road | Tucson, Arizona 85710

Office +1.520.239.4808 | Internal 814-4808

beaerospace.com

Passion to Innovate. Power to Deliver

This email (and all attachments) is for the sole use of the intended recipient(s) and may contain privileged and/or proprietary information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message.