1. Scott Roberts
  2. Valentina Database ADK
  3. Sunday, July 15 2018, 02:54 PM
  4.  Subscribe via email
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?
Comment
There are no comments made yet.
Ivan Smahin Accepted Answer
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
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Database ADK
  3. # 1
Scott Roberts Accepted Answer
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'
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Database ADK
  3. # 2
Ivan Smahin Accepted Answer
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?
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Database ADK
  3. # 3
Scott Roberts Accepted Answer
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
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Database ADK
  3. # 4
Ivan Smahin Accepted Answer
DateTrunc2() function will be available in 8.3.7.
http://www.valentina-db.net/bt/view.php?id=8318
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Database ADK
  3. # 5
  • Page :
  • 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