SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 date_trunc
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

kemi2299
Starting Member

United Kingdom
41 Posts

Posted - 04/04/2008 :  05:56:44  Show Profile  Reply with Quote
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

United Kingdom
1208 Posts

Posted - 04/04/2008 :  06:02:27  Show Profile  Reply with Quote
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
Go to Top of Page

elancaster
A very urgent SQL Yakette

United Kingdom
1208 Posts

Posted - 04/04/2008 :  06:04:07  Show Profile  Reply with Quote
isn't date_trunc postgresql? you are using SQL Server right?

Em
Go to Top of Page

harsh_athalye
Flowing Fount of Yak Knowledge

India
5581 Posts

Posted - 04/04/2008 :  06:18:10  Show Profile  Visit harsh_athalye's Homepage  Click to see harsh_athalye's MSN Messenger address  Send harsh_athalye a Yahoo! Message  Reply with Quote
Duplicate here: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=100288

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"
Go to Top of Page

kemi2299
Starting Member

United Kingdom
41 Posts

Posted - 04/04/2008 :  06:46:37  Show Profile  Reply with Quote
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=
Go to Top of Page

elancaster
A very urgent SQL Yakette

United Kingdom
1208 Posts

Posted - 04/04/2008 :  06:50:21  Show Profile  Reply with Quote
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




Em
Go to Top of Page

kemi2299
Starting Member

United Kingdom
41 Posts

Posted - 04/04/2008 :  09:12:32  Show Profile  Reply with Quote
not really as its suppose to pick the month and change it from 01/08 to january 08
Go to Top of Page

elancaster
A very urgent SQL Yakette

United Kingdom
1208 Posts

Posted - 04/04/2008 :  09:17:05  Show Profile  Reply with Quote
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
Go to Top of Page

kemi2299
Starting Member

United Kingdom
41 Posts

Posted - 04/04/2008 :  09:43:10  Show Profile  Reply with Quote
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.
Go to Top of Page

elancaster
A very urgent SQL Yakette

United Kingdom
1208 Posts

Posted - 04/04/2008 :  09:54:05  Show Profile  Reply with Quote
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

Em
Go to Top of Page

kemi2299
Starting Member

United Kingdom
41 Posts

Posted - 04/04/2008 :  10:40:39  Show Profile  Reply with Quote
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 ;
Go to Top of Page

harsh_athalye
Flowing Fount of Yak Knowledge

India
5581 Posts

Posted - 04/04/2008 :  10:49:15  Show Profile  Visit harsh_athalye's Homepage  Click to see harsh_athalye's MSN Messenger address  Send harsh_athalye a Yahoo! Message  Reply with Quote
What is the data type for Attendance_Cat_Cd field?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"
Go to Top of Page

kemi2299
Starting Member

United Kingdom
41 Posts

Posted - 04/04/2008 :  11:01:03  Show Profile  Reply with Quote
it char datatype
Go to Top of Page

harsh_athalye
Flowing Fount of Yak Knowledge

India
5581 Posts

Posted - 04/04/2008 :  11:13:23  Show Profile  Visit harsh_athalye's Homepage  Click to see harsh_athalye's MSN Messenger address  Send harsh_athalye a Yahoo! Message  Reply with Quote
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


Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"
Go to Top of Page

kemi2299
Starting Member

United Kingdom
41 Posts

Posted - 04/04/2008 :  11:24:05  Show Profile  Reply with Quote
it does not seem to work,
Go to Top of Page

harsh_athalye
Flowing Fount of Yak Knowledge

India
5581 Posts

Posted - 04/04/2008 :  11:29:16  Show Profile  Visit harsh_athalye's Homepage  Click to see harsh_athalye's MSN Messenger address  Send harsh_athalye a Yahoo! Message  Reply with Quote
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"
Go to Top of Page

kemi2299
Starting Member

United Kingdom
41 Posts

Posted - 04/04/2008 :  11:44:47  Show Profile  Reply with Quote
i am converting to int;
the feild is in char with nos but needs to convert it to integer
Go to Top of Page

harsh_athalye
Flowing Fount of Yak Knowledge

India
5581 Posts

Posted - 04/04/2008 :  11:53:49  Show Profile  Visit harsh_athalye's Homepage  Click to see harsh_athalye's MSN Messenger address  Send harsh_athalye a Yahoo! Message  Reply with Quote
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"
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22765 Posts

Posted - 04/05/2008 :  02:24:08  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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

Edited by - madhivanan on 04/05/2008 02:24:53
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22765 Posts

Posted - 04/05/2008 :  02:30:37  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000