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

Jumat, 22 April 2016

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.