[RESOLVED] SQL Date Issue

[Strider]

Im having an issue when inserting a date into my sql server table.

the table's date column is set to smalldatetime

so when i try to insert the date 23/03/2005 into the column, when i look on sql server the field contains 01/01/1900

however if i insert 03/23/2005, when i look on sql server the field contains 23/03/2005

why is this happening..... my regional settings are set to GMT.

[Graff]

There's nothing wrong, thats just the way SQL stores dates.

[Strider]

but why is it requiring me to insert the date in american format.

why cant i insert as the european format

when i insert as european date it gives me an error as it recognises that the 23 is not a month

[szlamany]

but why is it requiring me to insert the date in american format.

why cant i insert as the european format

when i insert as european date it gives me an error as it recognises that the 23 is not a month

SQL Server is very date biased to the American format.

We always use YYYY-MM-DD when doing date INSERT's - so that ambiguity does not become a problem.

[Strider]

to make things all the more strange.......

my application was working fine for the past two weeks then suddenly in the past few days its wont allow me to insert a date.now() into the date field....

i get the error i previously mentioned....


however, however...... when u insert date.today() it sets the date in the field to the 01/01/1900 ...and that aint the right date....

what the hell is going on.

[szlamany]

to make things all the more strange.......

my application was working fine for the past two weeks then suddenly in the past few days its wont allow me to insert a date.now() into the date field....

i get the error i previously mentioned....


however, however...... when u insert date.today() it sets the date in the field to the 01/01/1900 ...and that aint the right date....

what the hell is going on.

01/01/1900 is a "blank" date getting inserted into the date field.

[Strider]

what do you mean by blank date????

i just dont understand why date.today() gives me 01/01/1900 and date.now() gives me the error that there is no such month as the 23rd

[Graff]

Instead of letting using date.now() or whatever just use SQLs built in Now()

ie.

strSQL = "INSERT INTO TableX VALUES(" + id + ",'" + var1 + "',Now()," + var2 + ")"

[Strider]

in my try catch i get the following error when using the Now() sql function

'Now' is not a recognized function name

here is my sql stmt

Dim selectString As String = _
"UPDATE tbl_UsersCompany SET " & _
"Company='" & SafeSqlLiteral(p_customer.Company) & "', " & _
"Address1='" & SafeSqlLiteral(p_customer.Addr1) & "', " & _
"Address2='" & SafeSqlLiteral(p_customer.Addr2) & "', " & _
"Address3='" & SafeSqlLiteral(p_customer.Addr3) & "', " & _
"Town='" & SafeSqlLiteral(p_customer.Town) & "', " & _
"RegionID= " & p_customer.Region & ", " & _
"Phone='" & SafeSqlLiteral(p_customer.Phone) & "', " & _
"Fax='" & SafeSqlLiteral(p_customer.Fax) & "', " & _
"Email='" & SafeSqlLiteral(p_customer.Email) & "', " & _
"URL='" & SafeSqlLiteral(p_customer.URL) & "'," & _
"Active= " & l_active & ", " & _
"SupportAgreement= " & l_suppAgreement & ", " & _
"LastModified= Now(), " & _
"LastModifiedUserId= 138" & _
" WHERE UCID= " & p_customer.UCID '& "'"

[Graff]

hmm it could be Today()

[Strider]

yup had a look there....its getDate() in sql and its works fine....coola peoples cheeers

[szlamany]

yup had a look there....its getDate() in sql and its works fine....coola peoples cheeers

Just to further the point...

GETDATE() is the SERVER time - not the workstation or PC time.

It's always more appropriate, in my opinion, to use the server time and not the workstation time.

[Strider]

ya i think ill use that more often instead

[naeem55]

Hi,

I went throught your reply I have the same problem. Now in my case the system has been running for 4 months and this month on the 13th of june it started giving problems. When I checked the database (MS SQL) I found the date had reversed only for this month instead of saving as 6/1/2005 (June 1 2005) it started saving as 1/6/2005 (January 6 2005) and so when it came to 13 of this month the system gives an error message date out of range.

When saving data I format it to yyyy/mm/dd as this format is international according to MSDN. I would appreciate if anyone can point out something that might help.

Thanks

[Strider]

are you using the getdate() function because it will get the time from the sql server and insert that into the datetime field in the format of the regional settings on the server

just make sure to do a check on your server that the regional settings are set to UK or Ireland and not US so that the date format is dd/mm/yyyy.