DateTime format problem - 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

SQL Server 2000 Forums

MSDE (2000)

DateTime format problem

Author

Topic

macnab
Starting Member

6 Posts

Posted - 2008-06-18 : 02:45:52

Hi

One of the tables handles dates as dd/mm/yyyy (wanted format). Another table handles dates as mm/dd/yyyy (unwanted format).

Can I change the datetime format of the table? I have an idea it has something to do with locale.

In my code datetimes are dd/mm/yyyy. And working correctly as I can use myDate = Convert.ToDateTime("28/11/2008").

But conversion fails when I use
INSERT INTO table (column) VALUES('myDate')
also if I use
INSERT INTO table (column) VALUES('myDate.ToString("dd/MM/yyyy")')

Both return error convert from char data to datetime.

Thanks,
Nigel

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2008-06-18 : 02:56:10

This is to do with the regional settings of the machine the tables sit on. If your local machine is set to US, you will see the date as mm/dd/yyyy. Check the regional settings of your client and the settings of your server. Try the following and see if it works for you

INSERT INTO table (column) VALUES(Convert(varchar,mydate,120))

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-06-18 : 09:26:55

1 Always use proper DATETIME datatype to store dates
2 As long as you use datetime parameters and express dates in universal format YYYYMMDD HH:MM:SS you dont need to worry about the server's or local system's date settings
3 Formation matters only if you want to show them in front end application

Madhivanan

Failing to plan is Planning to fail

macnab
Starting Member

6 Posts

Posted - 2008-06-18 : 09:53:25

Your point 2 is basically what my problem was. The default ShortDate format for South Adrica is MM/dd/yyyy. So this is the format expected by MSDE.

But we use dd/MM/yyyy, so we Customise Regional Settings, which of course MSDE ignores.

So when I use INSERT INTO I need to use myDate.ToString("MM/dd/yyyy").

And this problem only exists because the value in myDate is created by using myDate = Convert.ToDateTime with a string that forces the 1st of the current month. If I use AddDays to force today's date to the first of the month the problem goes away.

Nigel

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-06-18 : 10:27:56

quote:
Originally posted by macnab

Your point 2 is basically what my problem was. The default ShortDate format for South Adrica is MM/dd/yyyy. So this is the format expected by MSDE.

But we use dd/MM/yyyy, so we Customise Regional Settings, which of course MSDE ignores.

So when I use INSERT INTO I need to use myDate.ToString("MM/dd/yyyy").

And this problem only exists because the value in myDate is created by using myDate = Convert.ToDateTime with a string that forces the 1st of the current month. If I use AddDays to force today's date to the first of the month the problem goes away.

Nigel

Always use DateTime variables and parameters. Don't convert any dates to strings, and don't concatenate those strings together into SQL statements; always use proper data types and PARAMETERS when passing data to a SQL statement from your client.

http://weblogs.sqlteam.com/jeffs/archive/2006/07/21/10728.aspx

- Jeff
http://weblogs.sqlteam.com/JeffS

macnab
Starting Member

6 Posts

Posted - 2008-06-19 : 02:58:40

Thanks for the tip.

Converted my code to:
cn = new SqlConnection(cnStr);
SqlCommand cmd = new SqlCommand("", cn);
cmd.CommandText = "INSERT INTO (User_ID, WhichMonth) " +
"VALUES(@user_ID, @month)";
cmd.Parameters.Add("@user_ID", SqlDbType.Int).Value = user_ID;
cmd.Parameters.Add("@month", SqlDbType.DateTime).Value = month;
try
{
cmd.Connection.Open();
cmd.ExecuteNonQuery();
cmd.Connection.Close();
}

I get "Syntax error near '('"
Is there an obvious error in my code?
Is there any way I can see the actual SQL string passed to the server too see what the error could be?

Nigel

macnab
Starting Member

6 Posts

Posted - 2008-06-19 : 03:10:50

I accidentaly deleted my tablename!!!

Works now thanks.

Nigel

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources