Awkward SQL

I have a query I need which is nothing short of awkward.I have a field which is a date/time type storing data as a full date/time i.e.: 2004-09-03 10:37 I need to select them so that the value returned is 2004-09-03 00:00 but more than this I need 3pm to be the date change. For example.

Re: Awkward SQL

James Smith wrote: I have a query I need which is nothing short of awkward.I have a field which is a date/time type storing data as a full date/time i.e.: 2004-09-03 10:37 I need to select them so that the value returned is 2004-09-03 00:00 but more than this I need 3pm to be the date

Re: Awkward SQL

SELECT CAST(your_datetime_field + INTERVAL '11 hours' AS DATE) FROM table or if you're not fortunate enough to have standards compliant db like postgres,say sql server: SELECT CAST(CONVERT(char(12),DATEADD(minute,541,yourDateColumn),101) AS smalldatetime) AS yourNewDateTime [Todays Threads]

RE: Awkward SQL

2004-09-03 08:00 -- 2004-09-03 00:00 2004-09-03 14:00 -- 2004-09-03 00:00 2004-09-03 15:01 -- 2004-09-04 00:00 SELECT CAST(your_datetime_field + INTERVAL '11 hours' AS DATE) FROM table At first glance this looks good and the MySQL docs seem to support this theory but it just throws an

Re: Awkward SQL

James Smith wrote: 2004-09-03 08:00 -- 2004-09-03 00:00 2004-09-03 14:00 -- 2004-09-03 00:00 2004-09-03 15:01 -- 2004-09-04 00:00 SELECT CAST(your_datetime_field + INTERVAL '11 hours' AS DATE) FROM table At first glance this looks good and the MySQL docs seem to support this theory but

RE: Awkward SQL

SELECT CAST(your_datetime_field + INTERVAL '11 hours' AS DATE) FROM table or if you're not fortunate enough to have standards compliant db like postgres,say sql server: SELECT CAST(CONVERT(char(12),DATEADD(minute,541,yourDateColumn),101) AS smalldatetime) AS yourNewDateTime

RE: Awkward SQL

2004-09-03 08:00 -- 2004-09-03 00:00 2004-09-03 14:00 -- 2004-09-03 00:00 2004-09-03 15:01 -- 2004-09-04 00:00 SELECT CAST(your_datetime_field + INTERVAL '11 hours' AS DATE) FROM table At first glance this looks good and the MySQL docs seem to support this theory but it just

Re: Awkward SQL

James Smith wrote: 2004-09-03 08:00 -- 2004-09-03 00:00 2004-09-03 14:00 -- 2004-09-03 00:00 2004-09-03 15:01 -- 2004-09-04 00:00 SELECT CAST(your_datetime_field + INTERVAL '11 hours' AS DATE) FROM table At first glance this looks good and the MySQL docs seem to support this theory but

Re: Awkward SQL

Jochem van Dieten wrote: Does the following work: SELECT MessageDate + INTERVAL 11 HOUR AS DATE FROM table That should be: SELECT MessageDate + INTERVAL 11 HOUR FROM table Jochem [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and

Re: Awkward SQL

Now I can do this by looping over a query and stinking the values into an array but is it possible to do this directly in the SQL? My feeling is that it would probabily be rather difficult with MySQL. I would do it in a loop in CF. -- ___ REUSE CODE! Use

RE: Awkward SQL

Schneegans [mailto:[EMAIL PROTECTED] Sent: 03 September 2004 12:17 To: CF-Talk Subject: Re: Awkward SQL Now I can do this by looping over a query and stinking the values into an array but is it possible to do this directly in the SQL? My feeling is that it would probabily be rather difficult

RE: Awkward SQL

Ok, extension of this problem SELECT CAST((MessageDate + INTERVAL 9 HOUR) AS DATE) AS Purchase_Date, ItemCount FROMtable Works just fine and returns the dates exactly as I want, unfortunately I need to be using sum(ItemCount) giving me... SELECTCAST((MessageDate + INTERVAL 9 HOUR) AS DATE)

RE: Awkward SQL

to Binary data if any one knows. -- Jay -Original Message- From: James Smith [mailto:[EMAIL PROTECTED] Sent: 03 September 2004 12:58 To: CF-Talk Subject: RE: Awkward SQL Ok, extension of this problem SELECT CAST((MessageDate + INTERVAL 9 HOUR) AS DATE) AS Purchase_Date