• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 907
  • Last Modified:

SQL Julian Dates

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
pgilfeather
Asked:
pgilfeather
  • 16
  • 8
  • 3
  • +2
1 Solution
 
jdlambert1Commented:
AND ServiceHistory.DateOfService < GetDate()
0
 
SweatCoderCommented:
AND DATEDIFF(day,ServiceHistory.DateOfService, getdate()) > 0
0
 
ShogunWadeCommented:
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
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
pgilfeatherAuthor Commented:
Sorry, tried both but neither worked.  Don't think it likes the GETDATE expression (returns it in pink?!)
0
 
ShogunWadeCommented:
it should go pink because GETDATE() is a built in sql function.
0
 
ShogunWadeCommented:
is your command in a stored procedure ?
0
 
jdlambert1Commented:
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
 
ShogunWadeCommented:
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
 
Anthony PerkinsCommented:
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
 
ShogunWadeCommented:
pg,    is MK_01_ServiceHistory ,etc in your SQL database?
0
 
jdlambert1Commented:
Try this:

AND ServiceHistory.DateOfService < Convert(smalldatetime, GetDate(), 101)
0
 
pgilfeatherAuthor Commented:
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
 
ShogunWadeCommented:
jd,   FYI     ServiceHistory.DateOfService is an integer representation of a julian date.
0
 
pgilfeatherAuthor Commented:
I am pulling the tables via ODBC drivers
0
 
ShogunWadeCommented:
pg,   in your dts step,  is the connection the SQL Server connection or the ODBC database connection?
0
 
ShogunWadeCommented:
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
 
pgilfeatherAuthor Commented:
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
 
ShogunWadeCommented:
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
 
ShogunWadeCommented:
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
 
pgilfeatherAuthor Commented:
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
 
ShogunWadeCommented:
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
 
ShogunWadeCommented:
try this out of curiosity,


AND ServiceHistory.DateOfService < #Date
0
 
ShogunWadeCommented:
also try substituting GETDATE() for DATE() or NOW()
0
 
pgilfeatherAuthor Commented:
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
 
ShogunWadeCommented:
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
 
pgilfeatherAuthor Commented:
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
 
ShogunWadeCommented:
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
 
pgilfeatherAuthor Commented:
More error messages I'm afraid!  
0
 
ShogunWadeCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 16
  • 8
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now