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.
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
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]
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
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
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
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
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
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
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
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
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)
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
13 matches
Mail list logo