C#+linq - Inserting DateTime value to the Db in the right format

views:

answers:

C#+linq - Inserting DateTime value to the Db in the right format

Hi,
I have a Db server with DateTime fields in the format of "yyyy-MM-dd mm:hh:ss"
I'm trying to use linq2sql to insert a DateTime member to a DateTime field in one of my tables.
When I do it in SQL I convert the DateTime as following:
"Insert into .... Convert(datetime, getdate(), 120) ..."
But when I try to submit the object from Linq the date time inserts in the wrong format.

Is there a way to define the format that will be inserted to the Db in Linq? Or is it a DateTime object Issue?

+5 A:

You shouldn't be dealing with a string format when you pass dates and times to the database, any more than you would if you were passing a number. The database should be handling all this for you without any conversions. This is true whether you're using LINQ or within the SQL - almost any time you have to manually do string conversions between types at the database level, you should look for a better solution.

If you read the value back out of the database (as a DateTime again) does it have the right value? If not, in what way is it wrong?

Jon Skeet 2010-03-09 09:25:21

I need that the format in the DB will be 'yyyy/MM/dd hh:mm:ss" and when I insert it from my code I get dd/MM/yyyy etc and it's not supposed to be like this that is why when we insert the values from regular queries I use the convert. I have a lot of systems running on that server and it's a given that I have to insert the date in this format, nothing I can do about it.Any ideas?

lnetanel 2010-03-09 09:42:13

well, you could do DateTime.ToString("yyyy/MM/dd hh:mm:ss"); when fetching from db?

sshow 2010-03-09 09:46:33

Nope, I'm dealing here with multiple systems. The insert to the Db is from a .Net WebService and the side that gets the data is a telephony system that has nothing to do with .Net :) that is why I need the correct format insert into the DB

lnetanel 2010-03-09 09:50:07

@lnetanel just because the DB shows you the datetime in the format 'yyyy/MM/dd hh:mm:ss' doesn't mean it is stored in that format also. Also, it would be helpful if you mentioned what kind of database you use.

ThanosPapathanasiou 2010-03-09 09:53:34

@Inetanel: Could you verify that this is **really** a DateTime field in the database, and not a string field? Because if it's genuinely a DateTime field, the internal representation is irrelevant in terms of the formatted version - different clients could format the data different ways if they wanted. Again, you shouldn't be *inserting* via strings at all.

Jon Skeet 2010-03-09 10:02:50

@ThanosPapathanasiou:1. The DB is SQL SERVER 20002. As I mentioned I have couple of systems that works together. when I insert the to the db with a regulaer SQL query with the convert method I see the yyyy/MM/dd hh:mm:ss format and when I'm inserting it using Linq I see the dd/MM/yyyy hh:mm:ss format and If I'll try to execute an sql query with the condition of getdate() > fieldDate I may encounter problems because of the wrong format. @Jon Skeet Yes this is really a DateTime field and not nvarchar or what ever :)

lnetanel 2010-03-09 10:08:00

@Inetanel: Just because the LINQ *log* shows it as a particular format doesn't mean it's being transferred as a string. That's just diagnostics, effectively. Imagine you were inserting a number instead of a datetime - do you think it would matter whether the log showed that number in hex or decimal? It's just a number after all. What does the data look like after you've inserted it?

Jon Skeet 2010-03-09 10:26:40

@Jon Skeet I didn't talk about the LINQ log, I talked about the actual data in the DB... in the DB itself it shows a different format then I need.

lnetanel 2010-03-09 10:31:07

@Inetanel: You talked about "when I'm inserting it using LINQ" which is why I assumed LINQ logs. But in the database itself it *doesn't have a string format*. That's simply not how it's stored. Again I ask: is the data *actually wrong* in the database after you've inserted it? When you fetch it from the other applications, do they get the wrong data? Ignore the formatting - concentrate on the data.

Jon Skeet 2010-03-09 10:32:54

@Jon Skeet I double checked this issue. you are right about the functionality of the my applications, they work as expected BUT still in the DB itself the date is represented not in the correct format and it can cause troubles in the future because in my telephony system the date is represented as a string once I get it from the db and if the string is not in the correct format I'll have big problems.... again, the representation of the date as string in the telephony system is a given, nothing I can do about it. any more suggestions?!

lnetanel 2010-03-09 11:31:37

@lnetanel look, however the datetime is represented in the database is irrelevant. If you are trying to insert to the table, simply convert your string to a System.DateTime with the format you are using and insert it.

ThanosPapathanasiou 2010-03-09 11:50:28

@Inetanel: Once the telephony app gets the data from the database it can format it however it wants - the internal representation in the database is irrelevant. That string format is determined by the app (or the SQL it uses) *not* be the representation in the database. Just to be clear about this, the database won't be storing it as a string in the first place. How your particular DB viewer happens to show you the data is entirely separate to the internal representation.

Jon Skeet 2010-03-09 11:53:40

A Google search -> http://www.sql-server-performance.com/articles/dev/datetime_datatype_p1.aspx "the presentation of the date/time format depends on the language or other computer settings"

ThanosPapathanasiou 2010-03-09 11:56:21

@ThanosPapathanasiou and @Jon Skeet You are right, I triple checked the issue on my production environment an it seams that even that the db viewer shows a different datetime format the telephony system gets the right format when it queries the table. still it's weird.. :) thanks for your help.

lnetanel 2010-03-09 12:25:05

no worries, datetime formats are a pain.

ThanosPapathanasiou 2010-03-09 12:32:35

programming is a pain

sshow 2010-03-09 12:39:13

pain is a pain!

lnetanel 2010-03-09 15:30:29

pain and simple

bzlm 2010-03-10 13:32:57

ansaurus

tags:

views:

answers:

C#+linq - Inserting DateTime value to the Db in the right format

related questions