|
i have gave my start date's. while i remove the timing, its work fine. but when the timing is included it fails. so when i m storing datetime, i always want to do in sepearate way only?
for example want to create columns as... Startdate-StartTime, EndDate-EndTime,MaxDate-MaxTime....??
help me - Karan
|
|
|
|
|
.
No
store date/time information as real DateTime, not as string, not as separate date and time.
But be aware that "between" may or may not exclude the values you specify, i.e. depending on the database "BETWEEN a and b" means "a <= x <= b" or "a < x < b"
 Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles]
I only read code that is properly formatted, adding PRE tags is the easiest way to obtain that. All Toronto weekends should be extremely wet until we get it automated in regular forums, not just QA.
|
|
|
|
|
On Oracle you could try
select * from tablename where trunc(startdate) between '23-Feb-2010' and '23-Feb-2010'
There is likely an equivalent for SQL Server.
[EDIT]
I'd also add that you are asking for a compare between two different database types so an implied conversion will take place. That conversion often causes results to be different than expected. Chris Meech
I am Canadian. [heard in a local bar]
In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
|
|
|
|
|
Try this:
select * from tablename where (datediff(day, startdate, '23-Feb-2010') = 0)
Adam
|
|
|
|
|
Hi All,
I'm working with SQL Anywhere 10 and one of the tables in our database has a password field we are wanting to encrypt in some way.
Is there a quick and easy way to do this? It's just so if anyone looks at the data in the database they can't see users passwords.
Thanks,
Mel
|
|
|
|
|
Hash the values before you insert them into the database.
I know the language. I've read a book. - _Madmatt
|
|
|
|
|
Hi Mark,
Thanks for your feedback, do you have an example of that?
Is there not a constraint in sybase or something along those lines that I can apply to the column?
Thanks again,
Mel
|
|
|
|
|
I have two table which are TBL_A and TBL_B.
TBL_A contain two record, TBL_B have no record.
Select ta.Name
From TBL_A ta
I get two record. But If wrote following query
Select ta.Name
From TBL_A ta, TBL_B tb
No any record get, because SQL Server defaultly use CROSS JOIN like this;
Select ta.Name
From TBL_A AS ta CROSS JOIN TBL_B AS tb
How can I close CROSS JOIN. I want to get record when TBL_B also have no record..
Actually I use "where" statement, but if one table have no record on FROM statement so I did not get result related about CROSS JOIN.
|
|
|
|
|
|
| Take a look at this Visual Representation of SQL Joins[^], it may help you understand what joins to use and how to use them Never underestimate the power of human stupidity
RAH
|
|
|
|
|
This may help
declare @tblA table(recordA varchar(10))
declare @tblB table(recordB varchar(10) null)
insert into @tblA select 'recA1' union all select 'recA2'
Query:
select a.* from @tblA a full join @tblB b
on a.recordA = b.recordB
Niladri Biswas
|
|
|
|
|
|
Hi All
When i try to install MSDE , i get the error message "The instance name
specified is invalid". On the server i have installed SQL Server 2000.
I am really confuse and i have searched a lot on the net. Kindly
provide me with a solution as its urgent, i will be highly grateful.M.Alizadeh
|
|
|
|
|
| If you did a normal install then the instance name should be the name or IP of the server it was installed on. If you installed a specific instance the name of the server should be servername/instancename. Never underestimate the power of human stupidity
RAH
|
|
|
|
|
thanks so much for your reply.
but can you tel me how i can change instance name??
please explain more i am a beginner...M.Alizadeh
|
|
|
|
|
mehrnoosh wrote: please explain more i am a beginner
Then you need to do some research, there are plenty of resources on the web and you should have Books On Line (BOL) with your SQL Server. Use them, research instancename.
I think you need to reinstall if you need to change the instance name but I'm not sure.Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hello Friends,
I've a date in string variable i want to convert this string into datetime format(mm/dd/yyyy) but it's giving me an error. Because my date is stored in dd/mm/yyyy but sql analyser require datetime in mm/dd/yyyy.
So please suggest me the way. Here is my example
Declare @str varchar(100),@strDate datetime
Set @str='14/02/2010'--(dd/mm/yyyy)
Set @strDate=@str
Print @strDate
|
|
|
|
|
Use the following
Declare @str varchar(100),@strDate datetime
Set @str='14/02/2010'--(dd/mm/yyyy)
Set @strDate=CONVERT(DATETIME, @str,103)
Print @strDate
Look into CONVERT in BOLNever underestimate the power of human stupidity
RAH
|
|
|
|
|
Try this
Declare @str varchar(100)
Set @str='14/02/2010'--(dd/mm/yyyy)
select ConvertedDate = CONVERT(varchar(10),CONVERT(datetime,@str,103),101)--mm/dd/yyyy
Output:
ConvertedDate
02/14/2010
 Niladri Biswas
|
|
|
|
|
try the below
SELECT CONVERT(VARCHAR(10), GETDATE(), 103) AS [DD/MM/YYYY]
|
|
|
|
|
.
.
.
SELECT PD_Users.FirstName as FirstName, PD_Users.LastName as LastName, PD_Users.UserName as UserName, PD_Users.CityID as CityID,
PD_Cities.CityName as CityName, aspnet_Membership.CreateDate as CreateDate,
(SELECT COUNT(*) FROM PD_Articles WHERE (PD_Users.UserName = UserName)) AS ArticleCount,
(SELECT COUNT(*) FROM PD_News WHERE (PD_Users.UserName = SenderName AND IsDevNews = 1)) AS NewsCount,
(SELECT COUNT(*) FROM PD_News WHERE (PD_Users.UserName = SenderName AND IsDevNews = 0)) AS ITNewsCount,
(SELECT COUNT(*) FROM PD_ForumMessage WHERE (PD_Users.UserName = UserName)) AS MessageCount,
CASE WHEN aspnet_Users.LastActivityDate > @DateActive THEN cast(1 as bit) ELSE cast(0 as bit) end AS IsOnline,
ROW_NUMBER() OVER (ORDER BY aspnet_Membership.CreateDate ) AS RowNumber
FROM aspnet_Users INNER JOIN aspnet_Membership ON aspnet_Users.UserId = aspnet_Membership.UserId INNER JOIN
PD_Users INNER JOIN PD_Cities ON PD_Users.CityID = PD_Cities.CityId ON aspnet_Users.UserName = PD_Users.UserName
WHERE (@LastName = PD_Users.LastName OR (@LastName = '' AND 1=1))
AND (@UserName = PD_Users.UserName OR (@UserName = '' AND 1=1))
AND (@CityID = PD_Users.CityID OR (@CityID = -1 AND 1=1))
AND (@UserStatus = IsOnline OR (@UserStatus = 0 AND 1=1))
.
.
.
Error:
Invalid name column 'IsOnline' (in Where statement)
How could I solve it?
thanks
|
|
|
|
|
Obviously IsOnline is not a column in the table. Find out what the column name should be
I know the language. I've read a book. - _Madmatt
|
|
|
|
|
Yes I know and there is not the same column name in the table but I have to compare it with @UserStatus
|
|
|
|
|
Try this[^]
I know the language. I've read a book. - _Madmatt
|
|
|
|
|
Is the UserStatus a string ? Then the IsOnline should be 'IsOnline' (in quotes).
Or is IsOnline a SQL Server function that you have written ? Maybe it needs a parameter.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
