var sidebar_align = 'right';
var content_container_margin = parseInt('290px');
var sidebar_width = parseInt('270px');
Unanswered: SQL Date Format Issue
I'm having a problem with inserting a date into a table. For example, if I wanted to insert today (and its not always today so no now() alowerd) into a table I do this:
insert into TableA (TheDate) Values ('18-12-06')
Now this is Australian date so 18th of December 2006.
It gives me an error:
Msg 242, Level 16, State 3, Line 2
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
The statement has been terminated.
But if I change the values around:
insert into TableA (TheDate) Values ('12-18-06')
But my code is passed to it in the first type. So how do I get SQL to accept it?
Thanks in advance.
use the "italian" style 5 in a CONVERT expression
insert into TableA (TheDate) Values (convert(datetime,'18-12-06',5))
Thanks r937. That works nicely.
However, this may be a question for another topic, but I'll try my luck anyway...
I'm using VB.Net as the front end. Creating it in VS 2005.
I've got three text boxes and am assigning them to variables:
I then set up all the connection and that works nicely. However, when I try and execute the insert command, it gives me an error.
Dim id As String = Me.cboID.Text
Dim eventDate As Date = Me.cboEventDate.Text
Dim eventTime As Date = Me.txtEventTime.Text
Insert into TableA Values ('" & id & "', convert(datetime,'" & EventDate & "',5), '" & EventTime & "');
Conversion failed when converting datetime form character string.
i have no idea, but i'm gonna guess you have a problem with the time value
say, why do you have separate date and time columns?
Even when you decide to keep the date and time seperate (not practical, but it is possible) try the same trick as R937 told you, but now using:
When you look into the column afterwarts, you'll see that the date is set to 1-1-1900 in the time column ... but you probably already noticed that.
> SELECT * FROM users WHERE clue > 0;
Empty set (0.00 sec)
Ok. I've been playing with the time format and found an alternative.
If I set the format by using:
Then it works. I believe it doesn't like the dd/mm/yyyy so the american style works.
As to having two separate date and time fields... good question. I was originally using postgreSQL but found that MSSQL's datetime actually stores both, so I may just use the one.
Thanks for your help everyone.
What about doing your display formatting at the application layer, which is actually displaying the data?