Dates & Database

[leeshadmi]

Hi,
I have a problem in my code and here is the description:
I use VB.Net 2008 Pro + SQL server 2005 Express.

I have a table that contain few date field and i use the local dates & time values to save it in the database using simple SQL code.

Here is what working for me and what not working for me:
OK:
[ScanDate] '11/10/2009'
[ScanTime] '8:51 AM'
[TimeStamp] '11/10/2009 8:51:05 AM'

Not OK:
[Date] '30/10/2007'
[ScanDate] '11/10/2009'
[ScanTime] '8:55'
[TimeStamp] '11/10/2009 8:55:14'

I guess it becuase the time missing the AM\PM but is there any way that i can convert: 8:55 to 8:55 AM?
Thanks

[HanneSThEGreaT]

What data type is ScanTime and TimeStamp fields ¿

[leeshadmi]

Thanks for your reply it's: datetime.
If you need the database file i can send it as well.

[dglienna]

If it's a different field, it may be 24-hour coded. Look for 13+ o'clock for 1pm going up to 23:00 (11pm)

[HanneSThEGreaT]

This may help Lee :

http://msdn.microsoft.com/en-us/library/ms187928.aspx

[leeshadmi]

HanneSThEGreaT Thanks again but this page it to complex for me and i didnt understand how to use the tips in it.

As far as i understand my problem is that my datetime field in the database are already exist and "wants to get" the format as in the US like "08:08 AM" and if i use the code in europe the the time will be "08:08" and the missing " AM: is given me the problems.

so as i wrote i didnt understand how to use the page you send me but i think i need to convert the time\ date format before i use the sql insert code.

I will try to create a small function that will convert the time format to the format i know is working and i will use it with hope that it will fix my problem.

dglienna thanks for tips as well...

[leeshadmi]

Ok Guyes i really need help here i just dont know what else to do.
I found my problem and its not related to the time it related to the date.

Here is my SQL line:

"INSERT INTO Recepit (BranchID, [Date], [SubTotal], [Tax], [Total], [RawData], [ScanDate], [ScanTime], [TimeStamp], [ImagePathA], [CategoryID], [PaymentTypeID], [ClientID], [ExpenseTypeID]) VALUES (3, '30/10/2007', '36.98', '3.05', '40.03', 'sos', '11/11/2009', '14:56', '11/11/2009 14:56:52', 'C:\Documents and Settings\Lee\My Documents\RS Databases\DB2_Images\Receipt_11_11_2009-14_55_20.jpg', 10, 9, 1, 1);"

Now the only value that make this line to not working is this: '30/10/2007' and i just dont understand why? if i will change it to '11/11/2009' it will work.
The date field is datetime as all other date fields in the table.

Any idea why it's not working?
Thanks

[dglienna]

This should help:

date: COALESCE(date_col, date '2007-03-19')
string: COALESCE(trim(date_col), 'blabla')

[leeshadmi]

dglienna Hi,
How can it helps? I dont understand , since i need to insert the values first to the DB.
Where and when can i use it?
Thanks

[Alsvha]

As people so patiently have tried to explain to you, your string of a date time does not compute with how the SQL Server recieves it.
You try to enter the date in a format it does not understand.
The link given by HanneSThEGreaT is the link which explains its and you really should read it thoroughly.

The problem is that when you give the SQL Server the date in the format dd/MM/yyyy but it reads it as MM/dd/yyyyy, meaning your 30/10/2007 is read as day number 10 in month number 30 which isn't a valid date.

You have multiple options at hand. You can cast the format you provide to another format, using the information in the earlier link.
You can change the date-string manually to a format the SQL Server understands, and the safe is to provide the date in a string like yyyy-MM-dd so "2007-10-30" should work. Or you can change it to 10/30/2007

This issue is a core issue in development, because date formats (amongst others) differ between nations and systems, so it is up to you to make sure the information is kept as neutral for the usage as possible.