Jumat, 22 April 2016

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.



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.

 



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.





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.






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.


__._,_.___

Posted by: Duane Hookom <duanehookom@hotmail.com>
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (11)

Upgrade your account with the latest Yahoo Mail app
Get organized with the fast and easy-to-use Yahoo Mail app. Upgrade today!


.

__,_._,___

Tidak ada komentar:

Posting Komentar