The 1 indicates this is a 21st century date. A 20th century date would be prefixed with a zero (0).
Darrell
From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: Friday, April 22, 2016 3: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.
The information contained in this communication is highly confidential and is intended solely for the use of the individual(s) to whom this communication is directed. If you are not the intended recipient, you are hereby notified that any viewing, copying, disclosure or distribution of this information is prohibited. Please notify the sender, by electronic mail or telephone, of any unintended receipt and delete the original message without making any copies.
Blue Cross Blue Shield of Michigan and Blue Care Network of Michigan are nonprofit corporations and independent licensees of the Blue Cross and Blue Shield Association.
Posted by: "Embrey, Darrell" <DEmbrey@bcbsm.com>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (6) |
Tidak ada komentar:
Posting Komentar