CODEWITHSUNDEEP
asp.netsqlsql-serversql-server-2008
gofor.net
gofor.net

Reputation: 4298

select * tablename returns datetime with two different format

I need some advice about retrieving records from data table. I have one table contain column dateDBO as DateTime.I use select query like select * from tblUsers in two different scenario.Where in one(server) I get the dateDBO format like 01/01/2012 12:00:00 AM and in other(local) scenario I get like 01-Jan-00 12:00:00 AM.

Can any body please tell me how this is happen I know I can get this fix by specifying the particular column name with Cast or Convert,but this select * tblUsers return me some mysterious result how ??

*I am calling this from my asp.net (3.5) application.

thanks in advance.

sorry for English.

Update :: The same code when I deployed on the server is reflecting the date 01/01/2012 12:00:00 AM this but on my local its like 01-Jan-00 12:00:00 AM.So I am confuse that this is due to some Date & Time setting change and how this date time change affect to asp.net selection. my .net code

//set the command
objCommand = new OleDbCommand(strQueryText, objConnection);
//set the command type
objCommand.CommandType = CommandType.Text;
OleDbDataAdapter objDataAdapter = new leDbDataAdapter(objCommand); 
DataSet objLcDataSet = new DataSet(); 
objDataAdapter.FillSchema(objLcDataSet, SchemaType.Mapped); 
int lngRetVal = objDataAdapter.Fill(objLcDataSet);

Result Data :: 

dateDBO
1959-11-05 00:00:00.000 
1965-12-13 00:00:00.000 
1979-08-01 00:00:00.000 
1972-02-15 00:00:00.000 
1978-12-23 00:00:00.000 
1985-09-04 00:00:00.000 
1986-04-19 00:00:00.000 
1966-03-10 00:00:00.000 
1959-08-30 00:00:00.000

I would like to ask you all,the Regional Option field in Control Panel will affect to the query output ??

when I change this I found the result same as I get on the server.

Upvotes: 0

Views: 284

Answers (2)

CharlesX
CharlesX

Reputation: 128

Try this:

SELECT REPLACE(CONVERT(VARCHAR(9), dateDBO , 6) , ' ', '-') AS [DD-Mon-YY], CONVERT(VARCHAR(8), dateDBO , 108) as [HH:MM:SS], substring(convert(varchar(30), dateDBO , 9), 25, 2) from tblUser

Then you need to plus these three columns.

For more information, see http://www.sql-server-helper.com/tips/date-formats.aspx

Upvotes: 2

Rn.
Rn.

Reputation: 167

Try this dataset.table[0].rows[0]["dateDBO"].ToString("dd/MM/yyyy");

Upvotes: 0

Related Questions