Solved

SQL  Julian Dates

Posted on 2004-09-01
29
850 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
Comment Utility
AND ServiceHistory.DateOfService < GetDate()
0
 
LVL 11

Expert Comment

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

Expert Comment

by:ShogunWade
Comment Utility
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
Comment Utility
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
Comment Utility
it should go pink because GETDATE() is a built in sql function.
0
 
LVL 18

Expert Comment

by:ShogunWade
Comment Utility
is your command in a stored procedure ?
0
 
LVL 15

Expert Comment

by:jdlambert1
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
pg,    is MK_01_ServiceHistory ,etc in your SQL database?
0
 
LVL 15

Expert Comment

by:jdlambert1
Comment Utility
Try this:

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

Author Comment

by:pgilfeather
Comment Utility
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
Comment Utility
jd,   FYI     ServiceHistory.DateOfService is an integer representation of a julian date.
0
 

Author Comment

by:pgilfeather
Comment Utility
I am pulling the tables via ODBC drivers
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 18

Expert Comment

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

Expert Comment

by:ShogunWade
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
try this out of curiosity,


AND ServiceHistory.DateOfService < #Date
0
 
LVL 18

Accepted Solution

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

Author Comment

by:pgilfeather
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
More error messages I'm afraid!  
0
 
LVL 18

Expert Comment

by:ShogunWade
Comment Utility
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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

743 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

13 Experts available now in Live!

Get 1:1 Help Now