Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 882
  • 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

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