Problem trying to trunc datetime field

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Problem trying to trunc datetime field

Author

Topic

melanthea
Starting Member

2 Posts

Posted - 2008-01-23 : 10:33:37

Hi all,

I've just started using SQL Server and I'm having problems trying to trunc dates. In my application I have a table with information about calls and I need to group them by hour, day, month and year, and I would like to do it exactly in the same way that PostgreSQL or Oracle do, for example:

trunc(to_date('22-AUG-03'), 'YEAR') would return '01-JAN-03'
trunc(to_date('22-AUG-03'), 'MONTH') would return '01-AUG-03'

I need to store that grouped information in another table, so I'm trying to get for example:
22-AUG-03 01:00:00,
22-AUG-03 02:00:00 for hours,

22-AUG-03 00:00:00
23-AUG-03 00:00:00 for days and so on...

I'm trying to use convert function but I cant get the information in the way I need it.

Any kind of help will be apreciated. Thank you very much in advance.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-23 : 10:44:35

try:-
CAST(CAST(DATEPART(year,GETDATE()) AS varchar(4))+ CAST(DATEPART(m,GETDATE()) AS varchar(2))+ CAST(DATEPART(h,GETDATE()) AS varchar(2))AS int)-hour

DATEADD(d,DATEDIFF(d,0,GETDATE()),0) - days

CAST(CAST(DATEPART(year,GETDATE()) AS varchar(4))+ CAST(DATEPART(m,GETDATE()) AS varchar(2)) AS int)-months

DATEPART(m,GETDATE()) - year

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-01-23 : 14:37:09

This should do what you want, but my question is WHY force SQL to work like something else when it functions differently?

SELECT DATEADD(HOUR, DATEDIFF(HOUR, 0, GETDATE()), 0)
SELECT DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)
SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)
SELECT DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0)

EDIT: Forgot Month

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-01-23 : 19:19:54

The following links have functions that will find the start of Century, Decade, Year, Quarter, Month, Week, Day, Hour, 30 Minutes, 20 Minutes, 15 Minutes, 10 Minutes , 5 Minutes , x number of Minutes ,Minute , or Second.

Start of Time Period Functions:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=64755
Start of Week Function:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47307

Since you are new to SQL Server, you should look at the post below for links to various SQL Server Date/Time subjects.

Date/Time Info and Script Links:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=64762

CODO ERGO SUM

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-01-24 : 01:32:44

quote:
Originally posted by melanthea

Hi all,

I've just started using SQL Server and I'm having problems trying to trunc dates. In my application I have a table with information about calls and I need to group them by hour, day, month and year, and I would like to do it exactly in the same way that PostgreSQL or Oracle do, for example:

trunc(to_date('22-AUG-03'), 'YEAR') would return '01-JAN-03'
trunc(to_date('22-AUG-03'), 'MONTH') would return '01-AUG-03'

I need to store that grouped information in another table, so I'm trying to get for example:
22-AUG-03 01:00:00,
22-AUG-03 02:00:00 for hours,

22-AUG-03 00:00:00
23-AUG-03 00:00:00 for days and so on...

I'm trying to use convert function but I cant get the information in the way I need it.

Any kind of help will be apreciated. Thank you very much in advance.

Also make sure that you use proper DATETIME datatype to store dates

Madhivanan

Failing to plan is Planning to fail

melanthea
Starting Member

2 Posts

Posted - 2008-01-24 : 03:20:01

Hi again,

Thank you very much for your fast replies, it seems that I've solved my problem. I've also have had a look at the links of the functions about getting the start time period, but it worked perfectly, and IMHO easier, with Lamprey's solution:

quote:

SELECT DATEADD(HOUR, DATEDIFF(HOUR, 0, GETDATE()), 0)
SELECT DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)
SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)
SELECT DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0)

Again, thanks a lot.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources