get rid of time from SQL servers data by using ASPX

Hi,

I have the problem in accessing data from MS-SQL server by using Dreamwearver MX 's VB.NET page. Although it works well, in the date format, it also display time. Please see the following code.

SELECT student_ID, Material_ID, Borrow_Date, Due_Date, Renew_Date, Status, include_with
FROM dbo.Record
WHERE student_ID = @.student_ID

To remove time, I added the following code as follows. After that, I can preview and works well without having time.

SELECT student_ID, Material_ID, convert(varchar(10),Borrow_Date,103), convert(varchar(10),Due_Date,103), convert(varchar(10),Renew_Date,103), Status, include_with
FROM dbo.Record
WHERE student_ID = @.student_ID

But, when I browse, I get the runtime error. When I look more details in server, the error said " Parser Error Message: The server tag is not well formed." and error in <MM:DataSet"

Please help me.

Jon

Try to use alias for your columns after convertion:

convert(varchar(10),Borrow_Date,103) AS Borrow_Date, convert(varchar(10),Due_Date,103) AS Due_Date,

|||Many thanks indeed.It is working now.Jon|||

don't do the formatting in T-SQL / Database. Do it in the ASP. How are you going to sort it when the date is now :

21/03/2007

01/04/2007

Get Random record from SQL table

Hi,

I am in a situation where our developer is on leave (annual leave for a month), and I have to add a control to my website, which is in aspx apges.

To start with i have created a table in my SQL database. this table has records with one-liners from various movies, and the movie title. The tabel have 3 columns, i.e. ID, Liners, MTitle.

So i want to get random records on the page when ever its refreshed. I am totally non-coder/programmer guy.

I have got a SQL statement from the internet " SELECT TOP 1 * FROM <table name> ORDER By NEWID() "

So would anyone please help me out with it, as is it correct, how can i apply in the aspx pages.

Thank you.

the sql statement is correct. Just change the <table name> to your actual table name|||

Hi,

If you want to get random records from the database via a single SQL statememt, you may try the method below:

Assuming there is a unique identifier for each row, and that there is at least one record in the table, retrieving a random record can be quite easy. This method will work in SQL Server 7.0 and above (running on Windows 2000), but this could be a performance problem on larger tables / resultsets:

SELECT TOP 1 someColumn FROM someTable ORDER BY NEWID()

If you are not running on Windows 2000, then the following code will work if your table has a unique identifier (e.g. IDENTITY) column:

SELECT TOP 1 someColumn FROM someTable ORDER BY RAND((1000*IDColumn)*DATEPART(millisecond, GETDATE()))

Note that both of these methods also allow you to select 10 or 50 or 5000 random records, simply by altering the TOP parameter

Hope it helps.