Saving Dates in SQL2k

[warthog72]

I have three data fields I need to store in SQL Server 2k. These fields are being imported via a table in Access. I figured I could just write the information into these fields but apparently I cannot.

Field1 = DateTime (mm/dd/yyyy) in Access
SMALLDATETIME in SQL

Field2 = DateTime (hh:mm:ss AM/PM) in Access
SMALLDATETIME in SQL

Field3 = Same as Field1

I'm just opening up 1 record at a time from access and writing the data into the SQL table but it says INVALID DATE FORMAT.

What am I doing wrong? Is there a specific datatype or casting I have to do inorder to store hh:mm info?

Thanks in advance.

 

[nigelrivett]

If you are really inserting hh:mm:ss AM/PM then it should work (you will lose the seconds in a smalldatetime field of course bit it will truncate automatically).

You might be better off inserting in character format.

yyyymmdd hh:mm:ss
or
hh:mm:ss

Your mm/dd/yyyy format may encounter problems if anything involved has a non-USA data format.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[karthik555]

Warthog,

The best way to insert a date time value is to conver them into unambiguous format.

For eg. if you have values like this

input - 10/07/2003 in mm/dd/yyyy then the same converted into unambiguous format will be "07 OCT 2003". This value can be stored even in a Char(11) variable.


Declare @MyDate char(11)
SELECT @MyDate = '07 OCT 2003' --Write a routine to convert access date to this format
INSERT INTO MyTable(MyDateCol) VALUES (@MyDate)

Similarly if you have a date time value the same can be represented in string as '07 OCT 2003 21:51:50'

Besides this there is Convert() function in SQL to convert date values to any format.

Hope this will be useful

 

[warthog72]

I tried using the FORMAT function in access to format the date fields as "mm/dd/yy" and the time as "hh:mm" but still get the same result - INVALID DATE FORMAT.

Perhaps I didn't setup the field in SQL properly?

All my date fields in SQL are:

Data Type: smalldatetime
Length: 4
Allow Nulls: Yes

Thanks again.

 

[nigelrivett]

That should be ok to receive a date.
Try using the profiler to see what is being executed.

note that the format 07 OCT 2003 will still cause problems if you get a foreign language client but woud probably be ok for your current problem.
I always use yyyymmdd as it is always OK (note that yyyy-mm-dd is not).

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[warthog72]

Very strange, when I use the Format(mydate, "yyyymmdd&quot to write the date field, I get the Type Mismatch error immediately upon executing that line of code. When I use the "mm/dd/yyyy" format, I get the Invalid Date Format at the rst.update line call.

When I switch to the "yy mmm dd" format it works (for the date fields, now for the time fields...

When I omit the time fields, all the date fields are input just fine. Thanks everyone. The format I am using for the time field is "hh:mm".

Should I change this?

Do I need a different data type within SQL Server to store just hh:mm??

Thanks again!

 

[warthog72]

So, the answer is (to the best of my newfound understanding):

To store a date, you need 4 bytes.

To store a time, you need 8 bytes.

SQL stores date and time as 8 total bytes, but does not just store time. In order to store time, you must have the first 4 bytes representing the date as well.

I had to convert the time field to a datetime and make it 8 bytes. Then, using the format "hh:mm:ss", i had no problems at all.

Thanks again, and I'll keep checking this becuase all of your help and insight is greatly appreciated!!

 

[nigelrivett]

Looks like whatever you are using to insert is trying to convert.

try 1900 jan 01 hh:mm for the time.
SQL Serve considers day 0 as 1 jan 1900 so that is the equivalent of a time.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.