Showing posts with label datetime. Show all posts
Showing posts with label datetime. Show all posts

Monday, March 19, 2012

Manually Inserting Data in DateTime Field

Using Server Explorer in VB 2005, I am manually entering data in a table in a SQL Server 2005 Express database that includes a DateTime field. I have tried every conceivable format, but no matter what I try get one of these 2 errors:

1. String was not recognized as valid DateTime

2. Operand type class; text incompatible with DateTime

I have Googled this to death, but no example which involves trying to enter the data manually, say from Server Explorer.

Formats tried include all datetime formats (mmddyy, yymmdd, using dashes or slashes, enclosing in single quotes or pound signs).

I would appreciate if someone could please give me an example that I can literally insert without error.

hi,

I tried myself inserting via the VS designer the following:

2006-09-19 (it works)
2006/09/19 (it works)
19/09/2006 (it works)
19-09-2006 (it works)

the 2 last entries are dangerous as the server could misunderstand the datetime format... my pc has the italian locale set, so it could be ok, but I do advise to use a "general" ISO format that can not bring to misunderstanding, thus YYYY-MM-DD

regards

|||

Andrea, appreciate the response, but I tried your suggestions, and some related ones (09/19/2006), and they did not work. The error was Operand type clash; text incompatible with DateTime.

Again, I am opening the SQL Server 2005 Express database, then the table, from Server Explorer, and then choosing Show Table Data from the shortcut menu, and then typing the data into the datetime column field. Any further ideas would be appreciated.

|||I am an idiot. I was typing the data into the wrong field. Your suggestions work. Sorry for wasting your time.

Monday, March 12, 2012

Manipulating varchars as Datetime

Hi all,

I am a little weak at SQL, so bear with me.Smile

Can anyone give me an SP which uses "Between" to Display all Dates

BETWEENa fromDate and toDate?

Two conditions

1.Both Dates are stored asvarchar.

2. I should be able to get the dates even if years are different, say between 20th December 2006 and 3rd January 2007

3.Is there a way to extract the yearDiff?

Regards,

Naveen

Hi

Try Datediff function in SQLSERVER

Bye

|||

You can use DateDiff as stated by deepakleo however there's a few things you need to make sure takes place.

1. Need to make sure that since your dates are being stored as varchar that they are being stored in a format that can be cast as datetime.

2. You need to cast them as datetime to determine the date diff.

datediff("YYYY", Cast('December 20 2006' as datetime), Cast('January 3 2007' As DateTime))

The above will give you the difference in years between the 2 dates. If you want the difference in dates then use "dd" instead of "YYYY" and "mm" for the difference in months.