Solved

SQL  Julian Dates

Posted on 2004-09-01
29
851 Views
Last Modified: 2008-02-01
My problem is, unfortunately I am not beardy at all (our term for meaning technically advanced!), so I wonder if I could ask a question and hopefully get an answer that I can just slot right into my criteria?  I am pulling data using ODBC drivers from a package that uses KCML language (don't know if this is common or not).  When I try to restrict my data by date I get an error message from the source as it does not understand the language, however I have managed to get it to understand julian date format (I don't know how but it works!).  The problem is that I need the criteria to say 'pull everything with a date of less than today' therefore I need the julian function for today.  I have attached a copy of my statement below, could you take a look and let me know any suggestions?  
AND ServiceHistory.DateOfService < 'TODAY'

Much Obliged,

Smooth Skin (ie no beard)

0
Comment
Question by:pgilfeather
  • 16
  • 8
  • 3
  • +2
29 Comments
 
LVL 15

Expert Comment

by:jdlambert1
ID: 11953633
AND ServiceHistory.DateOfService < GetDate()
0
 
LVL 11

Expert Comment

by:SweatCoder
ID: 11953664
AND DATEDIFF(day,ServiceHistory.DateOfService, getdate()) > 0
0
 
LVL 18

Expert Comment

by:ShogunWade
ID: 11953725
Hmm. jd im puzzled why this didnt work

AND ServiceHistory.DateOfService <  DATEDIFF(day,'20000101 12:00',GETDATE())+2451545

what error number did your receive?
0
 

Author Comment

by:pgilfeather
ID: 11953741
Sorry, tried both but neither worked.  Don't think it likes the GETDATE expression (returns it in pink?!)
0
 
LVL 18

Expert Comment

by:ShogunWade
ID: 11953760
it should go pink because GETDATE() is a built in sql function.
0
 
LVL 18

Expert Comment

by:ShogunWade
ID: 11953780
is your command in a stored procedure ?
0
 
LVL 15

Expert Comment

by:jdlambert1
ID: 11953813
Query Analyzer uses pink to identify T-SQL functions. Is that where you see pink? If so, that's not a problem.

If you execute the statement in QA and the result site GetDate() in red, then that's a problem. Could you post the entire query?
0
 
LVL 18

Expert Comment

by:ShogunWade
ID: 11953831
jd,    this is a followup,   its worth checking out

http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_21113778.html#11953690 

for some background.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 11953864
ShogunWade,
>>AND ServiceHistory.DateOfService <  DATEDIFF(day,'20000101 12:00',GETDATE())+2451545<<
Should that not be:
AND ServiceHistory.DateOfService <  DATEDIFF(day,'20000101 12:00',GETDATE())+2415019

See here:
http://www.kcml.com/toolset/FAQs/genprog.html#One
0
 
LVL 18

Expert Comment

by:ShogunWade
ID: 11953871
pg,    is MK_01_ServiceHistory ,etc in your SQL database?
0
 
LVL 15

Expert Comment

by:jdlambert1
ID: 11953873
Try this:

AND ServiceHistory.DateOfService < Convert(smalldatetime, GetDate(), 101)
0
 

Author Comment

by:pgilfeather
ID: 11953876
Ok,  I thought it went pink because I'm a smooth skin and not a beardy like you guys   :)

ShogunWade, my comand is not in a stored procedure but in a DTS Import Wizard Statement.  

JD  Here is my entire statement:-

SELECT TargetRecords.TargetNumber,
TargetRecords.TitleInitlsSurname,
TargetRecords.Surname,
TargetRecords.FirstName,
TargetRecords.Initials,
TargetRecords.Title,
TargetRecords.Salutation,
TargetRecords.Address001,
TargetRecords.Address002,
TargetRecords.Address003,
TargetRecords.Address004,
TargetRecords.Address005,
TargetRecords.Postcode,
TargetRecords.TelephoneNumbers001,
TargetRecords.TelephoneNumbers002,
TargetRecords.TelephoneNumbers004,
TargetRecords.TelephoneNumbers003,
TargetRecords.MarketingPreference,
TargetRecords.EMailAddress,
VehicleRecords.VehicleNumber,
VehicleRecords.Description_,
VehicleRecords.ChassisNumber,
VehicleRecords.DateRegistered,
VehicleRecords.SalesDate,
VehicleRecords.LastServiceDate,
VehicleRecords.CCBHP,
VehicleRecords.EngineNumber,
VehicleRecords.FuelType,
VehicleRecords.TransmissionRefce,
VehicleRecords.KeyNumbers001,
VehicleRecords.MOTDueDate,
VehicleRecords.NewUsed,
VehicleRecords.ColourWheelbase,
VehicleRecords.RadioPassword,
VehicleRecords.SalesExecutive,
VehicleRecords.NextServiceDate,
VehicleRecords.ServIntlMonths,
VehicleRecords.ServiceIntlMiles,
VehicleRecords.DeliveryDate,
ServiceHistory.WipNoAftersales,
ServiceHistory.ServiceDetails,
VehicleRecords.RegistrationNumber,
ServiceHistory.InvoiceNumber,
ServiceHistory.InvoiceValue,
ServiceHistory.DateOfService,
ServiceHistory.Mileage,
Left(ServiceHistory.ServiceDetails,2) AS SrvBranchCode,
VehicleRecords.SalesLocation AS SalBranchCode
FROM MK_01_ServiceHistory ServiceHistory,
 MK_01_TargetRecords TargetRecords,
 MK_01_VehicleRecords VehicleRecords
WHERE VehicleRecords.VehicleNumber = ServiceHistory.PrimeKeyForRecord AND
 TargetRecords.TargetNumber = VehicleRecords.ASalesTargetMagic AND
 TargetRecords.TargetNumber <> 0  AND ServiceHistory.DateOfService < *********************************
0
 
LVL 18

Expert Comment

by:ShogunWade
ID: 11953892
jd,   FYI     ServiceHistory.DateOfService is an integer representation of a julian date.
0
 

Author Comment

by:pgilfeather
ID: 11953902
I am pulling the tables via ODBC drivers
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 18

Expert Comment

by:ShogunWade
ID: 11953916
pg,   in your dts step,  is the connection the SQL Server connection or the ODBC database connection?
0
 
LVL 18

Expert Comment

by:ShogunWade
ID: 11953939
ie.   is this query running on sql server or on your KLM system.    of its on the KLM one then it is quite possible that GETDATE is not a valid function on there.
0
 

Author Comment

by:pgilfeather
ID: 11953992
ShogunWade,  the query is running on the KLM system (I think).  My source is using the ODBC other drivers and then system is my ODBC connection but the destination is Microsoft OLE DB Provider for SQL Server.  
0
 
LVL 18

Expert Comment

by:ShogunWade
ID: 11954206
ac,  

i think that document is refering to the value using  CONVERT(datetime,0)  as offset.

trouble is as we all know day 0 is not consistent across multiple many products.    Their algorythm is flawed,  it also doesnt consider that julian dates start at midday
0
 
LVL 18

Expert Comment

by:ShogunWade
ID: 11954223
pg,   the problem is that GETDATE() is a sql function which is not an ansii standard odbc likes ansi standard stuff.   i think youll need to replace this with TODAY OR NOW
0
 

Author Comment

by:pgilfeather
ID: 11954278
ShogunWade,

Tried NOW but it didn't like that either, I got an error message at the end of the import telling me that the KCML drivers didn't like it.  TODAY won't parse!  I'm starting to think maybe there isn't a solution.  
0
 
LVL 18

Expert Comment

by:ShogunWade
ID: 11954312
There is another way but its a bit more technical and i am concerned that you will struggle with it too much.    Dont worry, we will work this out.
0
 
LVL 18

Expert Comment

by:ShogunWade
ID: 11954358
try this out of curiosity,


AND ServiceHistory.DateOfService < #Date
0
 
LVL 18

Accepted Solution

by:
ShogunWade earned 500 total points
ID: 11954368
also try substituting GETDATE() for DATE() or NOW()
0
 

Author Comment

by:pgilfeather
ID: 11954583
ShogunWade,

I tried NOW() and got it to work - yyyyyyyyyyyiiiiiiiiiiiiiiiiipppppppppeeeeeeeeeeeeee!  However, when I put in NOW()-1 (ie to get yesterday) it doesn't work.  Is there another way of telling it to subtract days from its calculation?  

Thanks
0
 
LVL 18

Expert Comment

by:ShogunWade
ID: 11954787
Cool.  

so your statement now looks like this.

AND ServiceHistory.DateOfService <  DATEDIFF(day,'20000101 12:00',NOW())+2451545

correct ?

if you want to reduce this by a day then you can change it to
DATEDIFF(day,'20000101 12:00',NOW())+2451544

or you might for clarity later do this

DATEDIFF(day,'20000101 12:00',NOW())+2451545 - 1

its then easier to see that you meant it rather than a typo of the long constant
0
 

Author Comment

by:pgilfeather
ID: 11954921
No, my statement now reads AND (ServiceHistory.DateOfService=NOW()).  This returns all records with a Date Of Service of today which is excellent. However to return yesterday I tried AND (ServiceHistory.DateOfService = NOW()-1) but it didn't like it.  Thanks again for your super-beardy help!
0
 
LVL 18

Expert Comment

by:ShogunWade
ID: 11955115
ah ok,   in that case you will need to find the approriate function to add days.

In SQL Server this is DATEADD but again this is not standard i dont think,  but try anyway doing ....

 AND (ServiceHistory.DateOfService=DATEADD(day,-1,NOW()))
0
 

Author Comment

by:pgilfeather
ID: 11955222
More error messages I'm afraid!  
0
 
LVL 18

Expert Comment

by:ShogunWade
ID: 11955482
i thought so.    

unfortunately ive gotta leave the office now,  but ill have a delve around for the correct function this evening and let you know if i find it  

in the mean time this article may help a bit
http://www.oreilly.com/news/sqlnut_1200.html
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now