datetime format - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Database Design and Application Architecture

datetime format

Author

Topic

TommSaha
Starting Member

8 Posts

Posted - 2009-03-06 : 05:52:20

Hi all.

Had a small database that records date of expiring job ads.

Locally, using SQL Server Management Studio Express, I have the field datatype as datetime.

On the aspx page, I use a jquery calendar popup so the user can click a date, and it inserts it in the UK format - dd/mm/yyyy.

Using LINQ to upload the content:

job.ClosingDate = txtClosingDate.text

This worked fine locally.

When I recreated the database on the webserver (SQL Server 2000) , this code no longer works,

but if I use: job.ClosingDate = DateTime.Now

it works fine, but puts it in the US format - mm/dd/yyyy

What do you think has changed? The database setup is the same.

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-03-06 : 06:04:22

run this on both servers,

select @@language

I think its just a difference of language.

You can use sp_helplanguage to look into details of settings for different languages.

TommSaha
Starting Member

8 Posts

Posted - 2009-03-06 : 07:26:56

on the server it returns - us_english, which explains a few things.

I tried changing the language of the server to English/uk but still having the same problem:

Conversion from string "30/04/2009" to type 'Date' is not valid.

Guess I need a way of doing this in vb.net as I want user to see the date in uk format, then maybe in sql it can be whatever format, and then back in the app, it is in uk format?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-03-06 : 07:30:10

quote:
Originally posted by TommSaha

on the server it returns - us_english, which explains a few things.

I tried changing the language of the server to English/uk but still having the same problem:

Conversion from string "30/04/2009" to type 'Date' is not valid.

Guess I need a way of doing this in vb.net as I want user to see the date in uk format, then maybe in sql it can be whatever format, and then back in the app, it is in uk format?

When sending dates to table, format it in yyyy-mm-dd format

Madhivanan

Failing to plan is Planning to fail

TommSaha
Starting Member

8 Posts

Posted - 2009-03-06 : 08:00:28

I'm getting damn confused here.

Database is on the webserver.

If I build my app locally (still connecting to db on server), this line of code works fine

job.ClosingDate = txtClosingDate.Text
(value will be 31/03/2009)

When I publish the app and test it across the network, the same code does not work!

I have tried:
job.ClosingDate = txtClosingDate.Text.ToString("MM/dd/yyyy")
and I get this error
Unable to cast object of type 'System.String' to type 'System.IFormatProvider'.

Here is how I display the date on the webpage:
<h4><%#Eval("ClosingDate")%></h4>
can I format that. I'm not bothered how it is stored in the database really. Ideally it will be UK format. but I at least need to to display on the webpage in UK format.

pratikasthana17
Starting Member

3 Posts

Posted - 2009-03-18 : 04:41:48

use split the date value by "/" and convert it into "yyyy-MM-dd"

like

dim olddate as string="30/04/2009"

dim newdate as string()=split(date,"/")

dim d as string=string.concat(string.concat(newdate(2),"-",newdate(1),"-",newdate(0))," 00:00:00")

it will work

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources