SQL Server Date data type

[Mike Lewis]

One of my clients is planning to upgrade from SQL Server 2005 to 2008 R2.

I note that 2008 (and above) support the Date data type. Earlier versions only offered a Datetime (and Smalldatetime). If you want to map a Datetime or Smalldatetime to a VFP Date field, you have to jump throuth a couple of hoops to get rid of the time element.

I'm thinking of suggesting to the client that we use the new Date data type in those cases where the time element is not needed. I'm hoping this will simplify the process of moving this data into and out of the VFP application.

However, I don't yet have access to 2008 or above, so I haven't been able to experiment with the new data type. I'm wondering if I am likely to encounter any problems with it, from a VFP perspective.

Does anyone have any experience of using the Date data type from within VFP (either via SQL pass-through or remote views)? Are there any known problems I need to watch out for?

My basic aim is simply to work with date fields from within VFP without having to worry about the unwanted time element.

Thanks in advance.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[ljzagorac]

Mike, I don't see any problem in both directions.
When uploading to SQL I use an easy way to convert VFP date into date data to SQL:

set cent on
set mark to '-'
set date ymd

 

[Olaf Doschke]

The only problem you still will face is the empty date, but that's well known, isn't it?

I'm also not sure about the range, but the typical dates since 1s January 1900 are covered anyway.

Also in SQL Server a date compares to a datetime with implicit conversion with a time portion of 0:00:00 am

That said I still don't use it, even though we have several datetimes that came from the date type to sql2005 and then later converted to sql2008r2.

You can get used to it, even though of the hoops for querying ranges, for example, which should end at the end of the end date, and not at o am of the end date.

InsertDate or LastUpdate are simply more precise, for example. And things like a deadline actually do also have a time portion.

In a few days I will start migrating another foxpro database to sql2008r2 and will perhaps find reasons tp keep dates as dates only.

Bye, Olaf.

 

[Mike Lewis]

Thanks to both of you for your replies.

Olaf, I'm aware of the issues with empty date and range comparisons. These are not a problem in this case, because by the time the data gets to main part of the application, my data management layer has converted them to VFP dates (converting NULLs to blanks). I've also checked the supported date range, and that's OK too.

What I was more worried about is the actual reading and writing of the dates in VFP. In other words, whether SQLEXEC() (or a remote view) would somehow get confused when faced with a SQL date field, given that VFP has not been updated to recognise them.

But I can see now that that's silly. It's the ODBC driver, not VFP, that has to know the data types in the target database. Presumably the SQL Server ODBC driver will be handle that more or less transparantly.

ljzagorac, I don't see why SET CENTURY, SET MARK, etc. should be relevant, given that those commands only affect the display of the dates within VFP. But thanks anyway for the comment.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[ljzagorac]

Mike, if you do a remote view updates to SQl table, you don't need any additional work concerning dates with this settings.

 

[Olaf Doschke]

Ok, I see. But as you say this is rather an issue of ODBC. The date type itselfs exists longer and VFPs direct interface towards ODBC can address the date type, eg via VFP ODBC driver towards DBFs .

So this is built in already quite a long time. SQL Server also is not the only database now supporting a date only type.

Bye, Olaf.

 

[Mike Lewis]

Olaf, yes I see all that now. I don't know why I didn't think of it earlier. Thanks.

ljzagorac, what you say confirms what I assumed to be the case. I guess your point about SET CENTURY, etc. might be relevant if I need to include literal dates in a SQL statement when using SQL pass-through. I don't think that will be an issue in this case, but I'll keep it in mind.

Based on this discussion, I'll suggest that they go ahead with this. I'll report back if any problems arise.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[Olaf Doschke]

In regard to Insert-SQL it's easiest to use parameterisation via ?ldDate and second easiest to use the non locale specific canonical string formats of 'YYYMMDD' or 'YYYYMMDD hh:mm:ss'.

It's easy to create them with DTOS(Date()) and TRANSFORM(TTOC(Datetime(),1),"@R ######## ##:##:##"), and all this is independant on environmental VFP settings as CENTRURY and such.

And you can also make use of the datetime stringformat TTOC(datetime(),3) gives you.

It's a bad habit to make date settings just for interfacing SQL Server or any other technical module. You should always be able to set date settings as needed for UI output and nothing more, nothing less, so this is always untouched, as you can't forget to reset these settings.

There are enough options to create date strings in machine readable format independant on any VFP or Windows settings, so make use of them, ljzagorac. And keep the date settings, as the users need them.

Bye, Olaf.