using ToAODate() while inserting data to SQL Server results in wrong date in table

Question

0.00/5 (No votes)

See more:

Hi,

I have to insert or update a current datetime value to a sqlserver table.
because I don't know the datetime culure settings for the sqlserver, inserting something like mm-dd-yyyy HH:mm fails on some systems or results in incorrect values when day and month is below 13.

Trying to create a workaround for this problem I insert the c# DateTime.Now.ToAODate(). this seems to wirk, but looking closer at the result in the database, every date is structural 2 days in the future (eg. when inserting the date 21-10-2010 15:40 I get 23-10-2010 15:40 in the database. For now I simply substract 2 days before inserting the data.

Is this a know problem or am'i doing something wrong?

greetings,

Posted 21-Oct-10 5:13am

rgerla

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Dylan Morley · Answer 1 · 2010-10-21T05:39:00

Solution 1

Can you not just use GETDATE()?

INSERT INTO 
    MyTable (IntField, StringField, DateField)
VALUES
    (1, 'test', GETDATE())

Posted 21-Oct-10 5:39am

Dylan Morley

DavidKiryazi · Answer 2 · 2010-10-21T12:45:00

Hi,

I found a post relating to this issue. It seems this is a known issue and you need to have a bit of a hack to fix it. Link is below:

Here[^]

If you have rights to the database and to modify tables, you can set the column properties for the date column to have a "Default Value or Binding" insert the TSQL method GETDATE() as the default value. That way you don't have to worry about having DateTime objects floating around in your code. The dates will get generated when the data gets written to the database.

Ofcourse, this is not always an appropriate method to use if you use datasets or other offline mechanisms.

good luck!
Dave