GetDate() in query generator doesn' return the time!

[Former Member]

If I issue the following statement in transact sql (via a qry in mssql 2005):

select getdate()

what I get is :2007-04-11 15:48:53.557

The same statement in query generator gives me: 04/11/07

Why the difference and how do I get the current time (from query generator - not vb code)?

Thanks!!

[former_member201110 · Accepted solution]

Hi John,

The time is actually returned to SBO by the getdate() command but SBO is automatically formatting the field according to your company settings. In most cases this will display a datetime field as just the date.

For display purposes you can do something like this:

select DocNum, CardCode, cast(getdate() as nvarchar) from ORDR where DocDate < getdate()

Kind Regards,

Owen

[Former Member · Accepted solution]

Hi Owen,

Thanks for your response.

I was afraid it had something to do with B1 - it's too smart for my own good!

Problem is that I really need to get the current time to determine if a draft document was created with the last hour, for an alert. I need to build a datetime field from createdate & doctime in ODRF and use datediff to determine the number of minutes since GETDATE().

Is there no way to get the time from B1? Or do you know where in B1 I can change the setting you're referring to? I tried CURRENT_TIMESTAMP but B1 cuts that off at the pass as well!

[former_member201110 · Accepted solution]

Hi John,

I don't think the getdate() command is going to be your problem, it's the way that SBO stores the DocTime value. Rather than an actual time, it stores it as a small int in the format hhmm (eg 1250 for 12:50pm). This makes it a real pain to compare the actual creation date with the current time.

However, this should work:

select

*

from ODRF

where

datediff(mi, case

when len(cast(DocTime as nvarchar)) = 3 then

dateadd(mi, (cast(substring(cast(DocTime as nvarchar), 1, 1) as int) * 60) + cast(substring(cast(DocTime as nvarchar), 2, 2) as int), DocDate)

else

dateadd(mi, (cast(substring(cast(DocTime as nvarchar), 1, 2) as int) * 60) + cast(substring(cast(DocTime as nvarchar), 3, 2) as int), DocDate)

end, getdate()) < 30

The above query will show any draft created in the last 30 mins (based on the server time).

Kind Regards,

Owen

[Former Member · Accepted solution]

Sweet, Ingenious, even - THANKS ALOT, OWEN!

Getdate works as long as it's evaluated on the sql server, as you said, so you've "cut SBO off at the pass".

In case someone else needs the code I've pasted it it here - (the substring length & start were incorrect for four byte time values)

select docentry, doctime,createdate from odrf 
where datediff(mi,
case 
 when len(cast(DocTime as nvarchar)) = 3 then
	 dateadd(mi, 
      (cast(substring(cast(DocTime as nvarchar), 1, 1) as int) * 60
     + cast(substring(cast(DocTime as nvarchar), 2, 2) as int)), 
     createdate )
 else
	 dateadd(mi, 
      (cast(substring(cast(DocTime as nvarchar), 1, 2) as int) * 60
     + cast(substring(cast(DocTime as nvarchar), 3, 2) as int)), 
     createdate )
 end,getdate()) < 60

[Former Member · Accepted solution]

hello... i want to create an alert that would notify me a week before the birthday of a customer... i've already added a user-defined field u_bdate... i have no idea how... please help...

[Former Member · Accepted solution]

Thanks owen for this code. It's very useful for me. I am planning to connect this query to OPOR with a pop-up alert that indicates from 12hrs generation of the document.

Best Regards,

JZA