|
I have data of a start date, which I imported into SQL from an excell spreadsheet in which the date was an nvarchar type. So I imported it as smalldatetime, with the hopes that when I used a calender on my webpage it would use the same format and work beautifully! but it doesn't! for some reason, the calender uses the format: yyyy/mm/dd and the format I have it in my database is yyyy/dd/mm (and that's how it came with the import with the smalldatetime type). And I have fiddled with it all day and can't figure out how to get them to the same format as I can't get my SQL database to change my data to that format.
Am I doing something wrong or is this a silly problem that someone can help me with?
Thanks!
|
|
|
|
|
DATETIME and SMALLDATETIME fields will always have the same format. You need to convert it to a string and specify the output format you would like to use. The method for doing this will depend on the language you are using but any language should have a method for formatting date/time strings.
If you are using .NET for your web pages the DateTime class has overloaded ToString() methods for formatting the string. If you want the output of your SQL query to be formatted then use the CONVERT function:
.NET (c#)
<br />
myDateObject.ToString("yyyy/dd/MM");<br />
T-SQL
<br />
SELECT REPLACE(SUBSTRING(CONVERT(VARCHAR, GETDATE(), 120), 1, 10), '-', '/') AS Date<br />
T-SQL is less flexible, so you either need to concatenate calls to DATEPART (or YEAR, MONTH, DAY) or do something like the example above (SUBSTRING and REPLACE along with the 'style' argument of the CONVERT function).
|
|
|
|
|
Thanks, I will try that tomorrow morning. Will try the T-SQL script as I will only be using sql to do my coding.
appreciate the help 
|
|
|
|
|
Substitute your date column for getdate() in this:
select replace(convert(varchar,getdate(),102),'.','/')
It gives you 2008/04/10
Bob
Ashfield Consultants Ltd
|
|
|
|
|
ok that's great for getting it into the correct format for todays date, but how do I get the last 20 000 rows of data to reflect the correct date format?!
|
|
|
|
|
I thought I was fairly clear, I said, replace the getdate() in this with your date column
so
select replace(convert(varchar,getdate(),102),'.','/')
becomes
select replace(convert(varchar,YOUR_DATE_COLUMN,102),'.','/')<br />
from YOUR_TABLE
Replacing YOUR_DATE_COLUMN and YOUR_TABLE with the names of your date column and your table name
Bob
Ashfield Consultants Ltd
|
|
|
|
|
oh sheez! ha, sorry man! didn't register in my head at all! bit of a blonde moment there!
Thanks so much!
|
|
|
|
|
kindly tel me how to handle Data Purging of previous month... We want to have the data of current month only...
|
|
|
|
|
I assume you have some kind of added date on your table(s), otherwise how will youi know the age of the data, but anyway:
delete tablea<br />
where addeddate < dateadd(month,-1,getdate())
This will delete anything over 1 month old today. Its easy enough to work on a month end/month start basis, I'll leave that for you to work out.
Also, if you have referential integrity between table you need to do the deletes in the correct order.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Ah, you cross posted. Please don't do that
Christian Graus
Please read this if you don't understand the answer I've given you
"also I don't think "TranslateOneToTwoBillion OneHundredAndFortySevenMillion FourHundredAndEightyThreeThousand SixHundredAndFortySeven()" is a very good choice for a function name" - SpacixOne ( offering help to someone who really needed it ) ( spaces added for the benefit of people running at < 1280x1024 )
|
|
|
|
|
I have a scenario wherein my application needs to import a data from excel file to SQL server table which is pre-defined. Now the problem is that sometimes an error occurs because of invalid format.
I want to display the error to the user so that he can correct the excel file & again upload the same.
I use DTS for importing the Data in my table from excel file.
Please suggest.
Thanks,
Amit
|
|
|
|
|
I have a scenario wherein my application needs to import a data
I use DTS for importing the Data
Are you saying that your application invokes the DTS package to import the data? The simplest method would be to log errors into an error table and look for new entries after the DTS package has run. You can then display the error to the users.
If you're using the Object Library to invoke the DTS package from code, you can trap error in your package call.
SG
Cause is effect concealed. Effect is cause revealed.
|
|
|
|
|
Hi,
I am using SQL server 2005. Please consider the following illustration to understand my question:
Query:
SELECT * FROM
PersonalDetails
INNER JOIN
PhoneDetails
ON
PersonalDetails.MemberID=PhoneDetails.MemberID
Suppose the query returns something as follows:
MemberID |Name | PhoneNumber
1 |John | 12334560
1 |John | 13468076
2 |Mary | 77890000
2 |Mary | 12347454
Now what i wish to do is combine all entries of John into one row
and that of mary into another, Something like this:
MemberID |Name | PhoneNumber | PhoneNumber
1 |John | 12334560 | 13468076
2 |Mary | 77890000 | 12347454
Is it possible?
I really require the result to be in the above format only.
Any help is appreciated.
Thanks in advance.
Siddy
|
|
|
|
|
Here's one possibility:
<br />
DECLARE @Person TABLE(PersonId INT, Name VARCHAR(10))<br />
DECLARE @Details TABLE(PersonId INT, Phone VARCHAR(10))<br />
<br />
INSERT INTO @Person VALUES (1, 'John')<br />
INSERT INTO @Person VALUES (2, 'Mary')<br />
<br />
INSERT INTO @Details VALUES (1, '8005551234')<br />
INSERT INTO @Details VALUES (1, '8665554321')<br />
INSERT INTO @Details VALUES (2, '8005558888')<br />
INSERT INTO @Details VALUES (2, '8665559999')<br />
<br />
SELECT * FROM @Person P<br />
INNER JOIN @Details D ON D.PersonID =P.PersonID<br />
<br />
SELECT P.PersonId, P.Name, D.Phone, D2.Phone FROM @Person P<br />
INNER JOIN @Details D ON D.PersonID = P.PersonID<br />
LEFT JOIN @Details D2 ON D2.PersonId = D.PersonId AND D2.Phone <> D.Phone<br />
WHERE D.Phone IN (SELECT TOP 1 Phone FROM @Details WHERE PersonId = P.PersonID ORDER BY Phone)<br />
<br />
Although, if there were anyway for you to modify the schema so each phone number had either some sequence number or a phoneType column which was unique w/in the scope of the MemberId it would be easier to write something that would perform a lot better. This will work with small to medium size tables, but not on a very large table with millions of rows.
|
|
|
|
|
iam passing a parameter in oracle then how ican know that the parameter is containg to_char or not
|
|
|
|
|
Here's my query:
SELECT TOP (1) COUNT(*),[Destination Telephone No], [Call Start Date]
FROM Calls
WHERE ([Destination Telephone No] IS NOT NULL) AND (Name = @name) AND ([Call Start Date] >= @start) AND ([Call Start Date] <= @end)
GROUP BY [Destination Telephone No], [Call Start Date]
ORDER BY Count(*) DESC
--@name, @start, @end are controls which I bind results to
Any help would be appreciated!
|
|
|
|
|
If you put this query in your database server, what does it return ?
Christian Graus
Please read this if you don't understand the answer I've given you
"also I don't think "TranslateOneToTwoBillion OneHundredAndFortySevenMillion FourHundredAndEightyThreeThousand SixHundredAndFortySeven()" is a very good choice for a function name" - SpacixOne ( offering help to someone who really needed it ) ( spaces added for the benefit of people running at < 1280x1024 )
|
|
|
|
|
no results...But I think I finally figured it out. I changed the "name = " to "name like' and it seems to be working now! not sure why as the name is exactly equal to the name in the database?! but anyways it works now
|
|
|
|
|
Daniel_Logan wrote: as the name is exactly equal to the name in the database
Is it? or are there trailing spaces - I've been caught by that before 
Bob
Ashfield Consultants Ltd
|
|
|
|
|
No, there where no trailing spaces hey. I bound a drop down list to the table and pulled out the names and filled them into the list. Then when I clicked a button I called the dropdrownlist and got the name directly from there. So it was exactly the same. When I changed that to "like" it made all the difference?! not sure why or how that works but it worked!
|
|
|
|
|
i agree. there was a similar case with me also a few days ago
Ashish Sehajpal
|
|
|
|
|
Hi all
Iam using an SSIS package to import existing data from Oracle tables to SQl 2005 tables. I need to remove the identity from the sql table import the data then set the udentity again to the sql table.
Any ideas?
|
|
|
|
|
Hi,
The only way I know of to turn off the identity property is to modify the system tables. Try this:
------------------------------------------------------------------------
sp_configure 'allow update', 1
go
reconfigure with override
go
update syscolumns set colstat = colstat - 1 /*turn off bit 1 which indicates it's an identity column */
where id = object_id('yourtable')
and name = 'youridentitycolumn'
go
exec sp_configure 'allow update', 0
go
reconfigure with override
go
------------------------------------------------------------------------
Put in yourtablename and youridentitycolumn names. Once this runs, do your update on the column, and then set the identity property back on:
------------------------------------------------------------------------
sp_configure 'allow update', 1
go
reconfigure with override
go
update syscolumns set colstat = colstat + 1 /*turn on bit 1 which indicates it's an identity column */
where id = object_id('yourtable')
and name = 'youridentitycolumn'
go
exec sp_configure 'allow update', 0
go
reconfigure with override
go
------------------------------------------------------------------------
Hope this helps .
Regards,
John Adams
ComponentOne LLC
|
|
|
|
|
You can use
SET IDENTITY_INSERT tablename ON
to insert explicit values into your identity column. Once your import is done, do
SET IDENTITY_INSERT tablename ON.
Remember, SQL Server will continue from the last known value inserted into the identity column.
SG
Cause is effect concealed. Effect is cause revealed.
|
|
|
|
|
Hi, thanks for looking
I would like to create an index with 2 columns
I have an addresses table
AddressID, UserID, Name, ...
Each user can have many addresses, a user can not have two addresses with the same name
I have a special type of address, where 0 is set as the UserID (They dont belong to a user, but to something else)
Is there any way to create an index that wont throw an error beacuase i have many address with 0 as the UserID and "Special Address" as the Name
How could i acoomplish this?
Or are Triggers my only choice?
Thanks in advance
Alexei Rodriguez
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
