1. Scott Roberts
  2. Valentina Database ADK
  3. Sonntag, Juli 15 2018, 02:54 PM
  4.  Abonnieren via E-Mail
I need clarification on the DATETRUNC function.

The example in the documentation shows:


SET PROPERTY DateTimeFormat OF DATABASE TO 'kYMD';
SET PROPERTY DateSeparator OF DATABASE TO '-';
SELECT DATETRUNC( '1997-10-23 15:16:10', 'day' )
=> '1997-10-01'


First, why is the result of the function 1997-10-01 and not 1997-10-23?

Second, when I use this SELECT statement in a query in Valentina Studio, the result is '1997-10-01 00:00:00:000'. Shouldn't the time portion be omitted from the result in this instance?
Kommentar
There are no comments made yet.
Ivan Smahin Akzeptierte Antwort
There is a misprint in the wiki, fixed.
DateTrunc always returns datetime-type value.
If you need the date part only you should use getDate():


select getdate( now() )
-- 07/16/2018
Kommentar
There are no comments made yet.
Scott Roberts Akzeptierte Antwort
The DATETRUNC function is still returning the first day of the month when 'day' is specified for the DatePart parameter.


PRINT DATETRUNC('10/21/1997', 'day');

-- Returns '10/01/1997 00:00:00:000'
Kommentar
There are no comments made yet.
Ivan Smahin Akzeptierte Antwort
This function is "truncating" first argument to the nearest valid datetime value.


select DATETRUNC('10/21/1997 19:31:59', 'year');
-- 01/01/1900 00:00:00:000
select DATETRUNC('10/21/1997 19:31:59', 'month');
-- 01/01/1997 00:00:00:000
select DATETRUNC('10/21/1997 19:31:59', 'day');
-- 10/01/1997 00:00:00:000
select DATETRUNC('10/21/1997 19:31:59', 'hour');
-- 10/21/1997 00:00:00:000
select DATETRUNC('10/21/1997 19:31:59', 'minute');
-- 10/21/1997 19:00:00:000
select DATETRUNC('10/21/1997 19:31:59', 'second');
-- 10/21/1997 19:31:00:000


Yes, it looks like function ignores the first arg's part after "datePart" (second arg). So both statements produce the same result.


select DATETRUNC('10/21/1997 19:31:59', 'year');
-- 01/01/1900 00:00:00:000
select DATETRUNC('01/01/1901 00:00:000', 'year');
-- 01/01/1900 00:00:00:000


Please, describe more detailed what do you expect to get?
Kommentar
There are no comments made yet.
Scott Roberts Akzeptierte Antwort
I'm expecting the function to truncate everything after the specified datePart. Below is what I expect to see:


For (kMDY) 10/21/1997 19:31:59:325 (kYMD) 1997/10/21 19:31:59:325

Truncate to year - 01/01/1997 00:00:00:000 1997/01/01 00:00:00:000
Truncate to month - 10/01/1997 00:00:00:000 1997/10/01 00:00:00:000
Truncate to day - 10/21/1997 00:00:00:000 1997/10/21 00:00:00:000
Truncate to hour - 10/21/1997 19:00:00:000 1997/10/21 19:00:00:000
Truncate to minute - 10/21/1997 19:31:00:000 1997/10/21 19:31:00:000
Truncate to second - 10/21/1997 19:31:59:000 1997/10/21 19:31:59:000
Kommentar
There are no comments made yet.
Ivan Smahin Akzeptierte Antwort
DateTrunc2() function will be available in 8.3.7.
http://www.valentina-db.net/bt/view.php?id=8318
Kommentar
There are no comments made yet.
  • Seite :
  • 1


There are no replies made for this post yet.
However, you are not allowed to reply to this post.

Categories

Announcements & News
  1. 0 subcategories
Valentina Studio
  1. 2 subcategories
Valentina Server
  1. 4 subcategories
Valentina Database ADK
  1. 0 subcategories
Valentina Reports ADK
  1. 0 subcategories
Other Discussions
  1. 2 subcategories
BETA Testing
  1. 0 subcategories
Education & Research
  1. 0 subcategories