date_trunc - SQL Server Forums

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

SQL Server 2000 Forums

Transact-SQL (2000)

date_trunc

Author

Topic

kemi2299
Starting Member

41 Posts

Posted - 2008-04-04 : 05:56:44

hi,

how do i declare this statement in Tsql as date_trunc is not working in ms server

att_date_day = date_trunc('day',a.att_date),
week = date_trunc('week',a.att_date),
month = date_trunc('month',a.att_date),

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-04-04 : 06:02:27

select DATEADD(DAY,DATEDIFF(DAY,0,a.att_date),0)
select DATEADD(month,DATEDIFF(month,0,a.att_date),0)
select DATEADD(week,DATEDIFF(week,0,a.att_date),0)
select DATEADD(year,DATEDIFF(year,0,a.att_date),0)

Em

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-04-04 : 06:04:07

isn't date_trunc postgresql? you are using SQL Server right?

Em

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-04-04 : 06:18:10

Duplicate here: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=100288

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

kemi2299
Starting Member

41 Posts

Posted - 2008-04-04 : 06:46:37

thanks for your earlier reply, yes, i am using ms server,

could you look at the query again, as i need to display the result in the att_date_day=
week_Att=
month_Att=

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-04-04 : 06:50:21

is it giving the result you wanted though? as in effectively rounding off the the day / week / month? is that what date_trunc would do?


select  DATEADD(DAY,DATEDIFF(DAY,0,a.att_date),0) as att_date_day
       ,DATEADD(week,DATEDIFF(week,0,a.att_date),0) as week_Att
       ,DATEADD(month,DATEDIFF(month,0,a.att_date),0) as month_Att
from YourTableName

kemi2299
Starting Member

41 Posts

Posted - 2008-04-04 : 09:12:32

not really as its suppose to pick the month and change it from 01/08 to january 08

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-04-04 : 09:17:05

ah... more like Harsh's reply no your other topic then? so for month...
select datename(month, getdate() ) + ' ' + cast(datepart(year, getdate() ) as varchar(4))

Em

kemi2299
Starting Member

41 Posts

Posted - 2008-04-04 : 09:43:10

sorry, don't mean it the way it sound, you have been very helpful and will try this next one and this time reply softly.

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-04-04 : 09:54:05

quote:
Originally posted by kemi2299

sorry, don't mean it the way it sound, you have been very helpful and will try this next one and this time reply softly.

lol, i didn't take offence

kemi2299
Starting Member

41 Posts

Posted - 2008-04-04 : 10:40:39

thanks, the first one work better.

i am also trying to do conversion from varchar to int, but get error cannot convert
SELECT count(Mrn) as counter, month_Att,Attendance_Cat_Cd
into #lcdq16b from #lcae
where CONVERT(varchar,Attendance_Cat_Cd )= 1
group by month_Att,Attendance_Cat_Cd ;

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-04-04 : 10:49:15

What is the data type for Attendance_Cat_Cd field?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

kemi2299
Starting Member

41 Posts

Posted - 2008-04-04 : 11:01:03

it char datatype

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-04-04 : 11:13:23

[code]SELECT count(Mrn) as counter, month_Att,Attendance_Cat_Cd
into #lcdq16b from #lcae
where CONVERT(varchar,Attendance_Cat_Cd )= 1
and Attendance_Cat_Cd like '%[0-9]%'
group by month_Att,Attendance_Cat_Cd [/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

kemi2299
Starting Member

41 Posts

Posted - 2008-04-04 : 11:24:05

it does not seem to work,

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-04-04 : 11:29:16

Change the order of WHERE conditions.

where Attendance_Cat_Cd like '%[0-9]%' and
 CONVERT(varchar,Attendance_Cat_Cd )= 1

BTW, why you are converting to varchar?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

kemi2299
Starting Member

41 Posts

Posted - 2008-04-04 : 11:44:47

i am converting to int;
the feild is in char with nos but needs to convert it to integer

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-04-04 : 11:53:49

Where you are converting to int? It seems to me that it is implicitly getting converted to int because you are comparing it with integer value i.e. 1

Have you seen my reply on 04/04/2008 : 11:29:16?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-04-05 : 02:24:08

quote:
Originally posted by harsh_athalye

Change the order of WHERE conditions.

where Attendance_Cat_Cd not like '%[^0-9]%' and
CONVERT(varchar,Attendance_Cat_Cd )= 1

BTW, why you are converting to varchar?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-04-05 : 02:30:37

Also think why you need to specify the length of varchar during convertion
http://sqlblogcasts.com/blogs/madhivanan/archive/2007/12/04/column-length-and-data-length.aspx

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources