| Author |
Message |
cajsoft
Newbie
Joined: 23 Feb 2009
Location: United Kingdom
Online Status: Offline
Posts: 23
|
  
Topic: convert SQL datediff to crystal
Posted: 27 Oct 2009 at 3:06am |
Hi,
I've been trying to use the datediff and dateadd function that I use in a SQL query, but cant get it to work.. could someone please help
thanks
select DATEADD(hour,8,DATEADD(week,datediff(week,0,getdate()),0))
|
 IP Logged |
|
|
|
lockwelle
Moderator
Joined: 21 Dec 2007
Online Status: Offline
Posts: 4374
|
Posted: 27 Oct 2009 at 5:30am |
change getdate() to CurrentDate or now or today. Week to w, hour to h. you would think that spelling out the interval would work, but it is not listed in help.
HTH
|
| IP Logged |
|
cajsoft
Newbie
Joined: 23 Feb 2009
Location: United Kingdom
Online Status: Offline
Posts: 23
|
Posted: 27 Oct 2009 at 5:39am |
Originally posted by lockwellechange getdate() to CurrentDate or now or today. Week to w, hour to h. you would think that spelling out the interval would work, but it is not listed in help.
HTH hi, I have tried changing it to CurrentDate.. but the formula doesnt like the 0 - DATEADD("h",8,DATEADD("w",datediff("w", 0,currentdate), 0))
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 27 Oct 2009 at 7:29am |
a few things.
"w" is used for weekday
"ww" is for week which I think is the equivalent to your SQL statement.
I am not sure I am deciphering the SQL correctly. You used a 0 in a couple of places that require a date field.
What exactly are you trying to accomplish with it? That might be easier to answer. Edited by DBlank - 27 Oct 2009 at 7:30am
|
| IP Logged |
|
cajsoft
Newbie
Joined: 23 Feb 2009
Location: United Kingdom
Online Status: Offline
Posts: 23
|
Posted: 27 Oct 2009 at 7:46am |
|
I'm trying to return the previous monday's date @18:00hrs
and the current mondays date @8:00 hrs.
ie 19/10/2009 18:00 - 26/10/2009 08:00
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 27 Oct 2009 at 8:04am |
|
what if today is Monday? Do you need it to go back 2 weeks?
|
| IP Logged |
|
cajsoft
Newbie
Joined: 23 Feb 2009
Location: United Kingdom
Online Status: Offline
Posts: 23
|
Posted: 27 Oct 2009 at 8:09am |
|
No.. if today is monday then the date is from last monday to this monday (today).
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 27 Oct 2009 at 8:25am |
Ok I these should work:
1 week ago Monday at 18:00:
dateadd("h",18,dateadd("d",-(weekday(currentdate,crMonday)+6),currentdate))
last Monday at 8:00:
dateadd("h",8,dateadd("d",-(if weekday(currentdate,crMonday)=1 then 0 else weekday(currentdate,crTuesday)),currentdate))
|
| IP Logged |
|
cajsoft
Newbie
Joined: 23 Feb 2009
Location: United Kingdom
Online Status: Offline
Posts: 23
|
Posted: 27 Oct 2009 at 8:28am |
|
thanks.. That looks like what I need..
C
|
| IP Logged |
|
|
|
