Senin, 25 April 2016

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

 

I searched the web and found some code I modified which should work for you:

dateadd(day,[fieldname] % 1000,dateadd(year,([fieldname] /1000) -1,convert(datetime,'12/31/1900')))

Duane Hookom, MVP
MS Access 


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



Wow.  If you got one!  It'd be faster than working with submitting a help desk to upgrade the sql server.

 

From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: Monday, April 25, 2016 9:58 AM
To: Access Professionals Yahoo Group
Subject: 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.



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.





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 (17)

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