Solved

SQL  Julian Dates

Posted on 2004-09-01
29
858 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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 

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
 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
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.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

820 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