SQL getdate -> Navision date/time

Jan79

Hello,

i need to fill a Navision Date- and Time-Field via SQL. (No, i don't need a datetime-field in Navision) I use "getdate()" to retrieve the datetime of the SQL server, but i don't know how to convert it to a navision Date- or Time-Field.
Looks for me like a common problem, if you want to insert Data via SQL in Navision, but unfortunatly i haven't found a solution for that problem.

Anyone there who could help me out?

Thanks in advance!

Cheers,
Jan

kapamarou

If you see your SQL database you'll see that date and time fields are DATETIME fields in SQL.

So a date field is just the SQL DateTime field with time 0:00:0 and the exception that 0D is 1/1/1753 0:00:00 (example: Posting Date: 18/4/2006 0:00:00 )

A time field is again a datetime field with the date part set to 1/1/1754 (Example: 1/1/1754 8:00:00 ) with the exception of the blank time which is
1/1/1753 0:00:00

So
'1753-01-01 00:00:00:000' will give you a blank time field.
'1754-01-01 12:34:13:000' will give you a 12:43:13 time field
'2009-01-28 00:00:00:000' will give you a 28/01/2009 date field and
'1753-01-01 00:00:00:000' will give you a 0D date field.

Anyway, this is what I see in my SQL server.

Hope this helps...

Jan79

Thanks for your info.
But i'm not sure how to generate those field-values. I mean like setting the time to 0:0:0 so it becomes a valid Navision- Datefield.

Cheers,
Jan

kapamarou

Are you asking for specific SQL commands for formatting / converting values and types?

kine

What about functions DT2DATE and DT2TIME? ;-)