|
rameez Raja wrote: i use varchar(50) field for stroing Date
Mistake 1 - this going to plague you until you fix the problem - do it NOW. Convert the varchar to a datetime field.
When storing the date make sure you store the DATE only not the date and time.
Use datepart to identify the date segments you require. I would create a view with the additional calculated fields and denormalised your structure for your reporting requirements.
However unless you fix mistake 1 your progress will be delayed as you try to work around the format limitations. Datetime is your friend, he has lots of nice bits to play with.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Thanks for replying and give me good suggestion .
i have to convert varchar to datetime field and have to store only date part into database table named Date (varchar) or i have to taken Datetime field type for storing date.
please suggest me as i get solution .
then how would be my query?
Best regards
rameez
|
|
|
|
|
Create another column name DateT as a datetime field
Convert your existing date data into the DateT field
When storing date data only store the date (today) unless you need the time component (now).
If you store the time component then you cannot select on '2008-Jul-15' as the actual value may be '2008-Jul-15 04:23:11' with the time component. This will force you to use between or format whenever you use the DateT field.
Try this
SELECT TOP 100
DateT
DATEPART(d,datadate) [Day],
DATEPART(wk,datadate) [wk],
DATEPART(mm,datadate) [mth],
DATENAME(weekday,datadate)
FROM Tablename
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I know some of you will say *what a rubbish*  ,
but, believe me, some people are looking at this rubbish and actually learn something.
So ... Don't say *Unhelpful Answer* 
Just copy and test (in SQL 2005)...
;
CREATE TABLE #T (caseno INT IDENTITY(1,1), Date_Cr DATETIME);
DECLARE @Cases INT; SET @Cases = 10000 ;
WHILE @Cases > 0
BEGIN
INSERT INTO #T (Date_Cr) SELECT GETDATE()-RAND()*365*4 ;
SET @Cases = @Cases - 1 ;
END ;
;
;
;WITH case_stats AS (
SELECT Period = YEAR(Date_Cr) * 10000
, Cases = COUNT(*)
FROM #T
GROUP BY
YEAR(Date_Cr) * 10000
UNION ALL
SELECT Period = YEAR(Date_Cr) * 10000
+ (( MONTH(Date_Cr) + 2 ) / 3)*1000
, Cases = COUNT(*)
FROM #T
GROUP BY
YEAR(Date_Cr) * 10000
+ (( MONTH(Date_Cr) + 2 ) / 3)*1000
UNION ALL
SELECT Period = YEAR(Date_Cr) * 10000
+ (( MONTH(Date_Cr) + 2 ) / 3)*1000
+ MONTH(Date_Cr)*10
, Cases = COUNT(*)
FROM #T
GROUP BY
YEAR(Date_Cr) * 10000
+ (( MONTH(Date_Cr) + 2 ) / 3)*1000
+ MONTH(Date_Cr)*10
UNION ALL
SELECT Period = YEAR(Date_Cr) * 10000
+ (( MONTH(Date_Cr) + 2 ) / 3)*1000
+ MONTH(Date_Cr)*10
+ (1 + DATEPART(ww, Date_Cr) - DATEPART(ww, CONVERT(NVARCHAR(7),Date_Cr, 121)+'-01'))
, Cases = COUNT(*)
FROM #T
GROUP BY
YEAR(Date_Cr) * 10000
+ (( MONTH(Date_Cr) + 2 ) / 3)*1000
+ MONTH(Date_Cr)*10
+ (1 + DATEPART(ww, Date_Cr) - DATEPART(ww, CONVERT(NVARCHAR(7),Date_Cr, 121)+'-01'))
)
SELECT *
INTO #S
FROM case_stats ;
;
;
;
SELECT
Period
, STUFF( STUFF( STUFF( STUFF( Period ,1,0,'Y') ,6,0,'Q') ,8,0,'M') ,11,0,'W')
, Cases
FROM #S
ORDER BY LEFT(Period,4) DESC, Period ;
;
SELECT Period,
Year = 'Y '+CASE WHEN RIGHT(Period, 4) ='0000'
THEN LEFT(Period,4) ELSE NULL END
, Quarter = 'Q '+CASE WHEN RIGHT(Period, 4)<>'0000' AND RIGHT(Period, 3)='000'
THEN RIGHT( LEFT(Period,5) ,1) ELSE NULL END
, Month = 'M '+CASE WHEN RIGHT(Period, 4)<>'0000' AND RIGHT(Period, 3)<>'000' AND RIGHT(Period, 1)='0'
THEN RIGHT( LEFT(Period,7) ,2) ELSE NULL END
, Week = 'W '+CASE WHEN RIGHT(Period, 4)<>'0000' AND RIGHT(Period, 1)<>'0'
THEN RIGHT(Period,1) ELSE NULL END
, Cases
FROM #S
ORDER BY LEFT(Period,4) DESC, Period ;
;
DROP TABLE #S ;
DROP TABLE #T ;
|
|
|
|
|
Hi All,
Iam new to SQL Server. Could you please help me in writing the script to set the timer for SSIS package for every 10 seconds(say).
Thanks in advance.
Regards,
nag
|
|
|
|
|
What do you mean?
Do you want this package to execute every 10 seconds?
|
|
|
|
|
Yes. I need that package to be executed by itself for every 10 seconds.
Thanks,
Veni
|
|
|
|
|
Maybe you could use this trick[^].
But the idea of an SSIS package executed every 10 seconds keeps bugging me. What kind of package is it? What does it do? Does it take less to 10 seconds to execute it?
|
|
|
|
|
(i can speak some engish so i hope you are understand o my writes)
using sql server 2005;
how to convertdatetime as yyyy/mm/dd hh:mm
i write a user defined function as below but that function result show as 2008-07-14 23:07:00.000.... i want result of function show as 2008-07-14 23:07 or 14-07-2008 23:07
CREATE FUNCTION [dbo].[TarihDuzenle] (@TARIH DATETIME)
RETURNS DATETIME
AS
BEGIN
SET @TARIH = CONVERT(
DATETIME, CONVERT(VARCHAR, DATEPART(YYYY,@TARIH)) +'/'+
CONVERT(VARCHAR, DATEPART(MM,@TARIH)) +'/'+
CONVERT(VARCHAR, DATEPART(DD,@TARIH)) + ' '+
CONVERT(VARCHAR, DATEPART(HH,@TARIH)) + ':'+
CONVERT(VARCHAR, DATEPART(MM,@TARIH)) + ':00'
)
RETURN @TARIH
END
|
|
|
|
|
Try this
select convert(varchar,getdate(),120)
It returns date in yyyy-mm-dd hh:mi:ss(24h) format.
Thanks,
Arindam D Tewary
|
|
|
|
|
thanks for your answer.
is possible not show second?
|
|
|
|
|
select convert(varchar<big>(16)</big>,getdate(),120)
modified on Tuesday, July 15, 2008 9:41 PM
|
|
|
|
|
I'm trying to use BULK INSERT to import a text file and then check its contents against a table in the database. I'm getting a problem when I compare the results however.
I'm making a temp table to hold the text file's contents
CREATE TABLE #Temp(Code varchar(5), number varchar(2))
Then using BULK INSERT to get the contents in
BULK INSERT #Temp FROM 'PATH.txt' WITH (FIELDTERMINATOR = ',',ROWTERMINATOR = '\n')
And then checking if any matches occur
SELECT Code, (CASE WHEN
(SELECT Count(DBTable.Code) From DBTableWHERE DBTable.Code= #Temp.Code) > 0
THEN "Yes"
ELSE "No" END) AS FLAG
From #TempCCServs
The problem is that I get the following error message now:
"Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation."
Anyone have an idea what I'm doing wrong?
Thanks for any help.
|
|
|
|
|
The two columns in the comparison differ in collation settings.
The following code applies sames collation to both sides before the comparison.
SELECT Count(DBTable.Code) From DBTable WHERE DBTable.Code collate Latin1_General_CI_AS = #Temp.Code collate Latin1_General_CI_AS
Hope that helps.
Regards,
Syed Mehroz Alam
|
|
|
|
|
My eval version that has been serving about 10 users in an intranet setup expired yesterday. I searched online and seems now it's all the 2005 versions on the market. Is there still anywhere I can find the 2000 version? Thanks.
btw for now my work around is just to reinstall the eval to get another 120 days.
run forest, run...
modified on Monday, July 14, 2008 2:20 PM
|
|
|
|
|
|
Using sql query its possible to find the last week dates?,
Mean Current date is 7/14/2008...,
So last week dates 7/7/2008 to 7/12/2008 have to come...,
Thanks & Regards,
NeW OnE,
please don't forget to vote on the post
|
|
|
|
|
to find first day of week use DATEPART() with the "dw" parameter
I Wish the Life Had CTRL-Z
Wizard's First Rule : People are fool,they believe what they want to believe or what they afraid to believe
www.subaitech.blogspot.com
|
|
|
|
|
SET DATEFIRST 1;
DECLARE @d DATETIME; SET @d = CONVERT(NVARCHAR(10), GETDATE(), 121);
DECLARE @x INT; SET @x = DATEPART(dw, @d) ;
WHILE @x > 1
BEGIN
SET @d=@d-1 ;
SET @x=DATEPART(dw, @d) ;
END ;
SELECT
CONVERT( NVARCHAR, DATEADD(wk, -1, @d) , 101) AS FirstDayOfLastWeek
, CONVERT( NVARCHAR, DATEADD(dd, -1, @d) , 101) AS LastDayOfLastWeek
;
|
|
|
|
|
Select Cast(Cast(GetDate() as int) as datetime) As Today
,Cast(Cast(DateAdd(dd, -7,
DateAdd(dd, - DatePart(dw, Getdate()) + 1,
GetDate())) as int) as datetime) As LastWeekStart
,Cast(Cast(DateAdd(dd, -1,
DateAdd(dd, - DatePart(dw, Getdate()) + 1,
GetDate())) as int) as datetime) As LastWeekEnd
If your wondering about the casting, it sets the time to 00:00:00.000
|
|
|
|
|
Hmm... I think this version is working only on Mondays... but that's OK, this was in fact the question about.
|
|
|
|
|
The answer before was a Monday morning answer, so I've got a better one now  :
select
Convert(varchar,DateAdd(dd, -(Datepart(dw, <code>Getdate()</code>) +6), <code>GETDATE()</code>), 101)
As PreviousWeekStart
,Convert(varchar,DateAdd(dd, -Datepart(dw, <code>Getdate()</code>), <code>GETDATE()</code>),101)
As PreviousWeekEnd
You can always replace the GETDATE() that I highlighted in the dark red with another date. This one works for any day of the week.
|
|
|
|
|
Thank u,And one more thing i want to find the last week is which saturday...,
It mean its 2nd or 4th saturday or odd saturday...,
Becoz based on that i want to calculate workdays
If its even(2nd or 4th) saturday then i want to show workdays 5
else if its odd(1st or 3rd) saturday then i have to show workdays 6 how to do that?,</b>
Plz help me
Thanks & Regards,
NeW OnE,
please don't forget to vote on the post
modified on Tuesday, July 15, 2008 1:10 AM
|
|
|
|
|
Hi,
My Table structure is look like this.....Code, Q1, Q2, Q3 and records are like...
Code Q1 Q2 Q3
1 Good Good Average
2 Poor Average Average
3 Good Excellent Average
4 Excellent Good Good
etc.....
I need to write a single query, which will give result look like.
Excellent Good Average Poor
Q1 1 2 0 1
Q2 1 2 1 0
Q3 0 1 3 0
Actually, this table is used to get the feedback from the users. User has given their feedback by selecting options. I need the details that how many persons answered Excellent for Question1 (Q1), Question2 (Q2), etc...As well as How many answered Good for Q1,Q2,Q3, etc....I need this format. Please help me.
Balasubramanian K.
|
|
|
|
|
SQL Server has a Pivot/Unpivot syntax, I used the unpivot for this. If your using SQL server, then this should work for you.
Select Question
,Sum(Case When Response = 'Excellent' Then 1 Else 0 End) as Excellent
,Sum(Case When Response = 'Good' Then 1 else 0 End) as Good
,Sum(Case When Response = 'Average' Then 1 Else 0 End) as Average
,Sum(Case When Response = 'Poor' Then 1 Else 0 End) as Poor
From
(Select Code, Q1, Q2, Q3
From <code>#t</code>) p
Unpivot
(Response For Question In
(Q1, Q2, Q3)) as unpvt
Group By Question
Just replace the #t (highlighted in the dark red) with your table name.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
