date/time fields

  • Thread starter Thread starter Jerome
  • Start date Start date
J

Jerome

Hallo,
I know a lot has already been told about date/time fields in a database but
still confuses me, specif when dealing with SQLserver(Express).
It seems that sqlserver only accepts the date in a "yyyyMMdd" format?
(difference between Express and MSDE2000A ?)
What is the one and only true way to deal with this problem in VB2005:
Local settings are Dutch (Belgium) ; thus date is in "dd/MM/yy" (or perhaps
dd/MM/yyyy) and time in "hh:mm:ss"

dim MyDateVar as string, MyIdVar as Integer
dim MyCommand = New Sqlcommand("",Connection)
MyCommand.commandtext = "Update MyTable Set MyDatefield = '" & MyDateVar &
"' Where MyIdField = " & MyIdVar = SomeIntegerValue
MyCommand.executenonquery

How to deal with the MyDateVar when:

1.
The Variable comes from a textbox knowing that the user puts in dd/MMyyyy
In this case there is no need to have the time with it.

2.
The date comes from a datetimepicker control
(MyDateVar = DtPicker.Value)?

3.
The date and time comes from the system
MyDateVar= Format(DateTime.Now, "yyyyMMdd") seems to work but
Format(DateTime.Now, "yyyyMMdd.hhmmss") gives a runtime error

So, any help and/or suggestion on this will be greatly appreciated.
Thanks and greetings to all
Jerome
 
Jerome,

It is very simple you should never use a date/time as a string, however
always as a DateTime field.

When you present that to a textbox, than you can use the overloaded toString
with the Iformatprovider
http://msdn.microsoft.com/library/d...globalizationdatetimeformatinfoclasstopic.asp

If you get it back you can use the Cdate
mydateField = Cdate(mytextbox.text)

And if you want to supply it to a database you use the parameters.
http://www.vb-tips.com/default.aspx?ID=886bba68-8a2f-4b99-8f66-7139b8970071

Maybe even better to show with this more extended but with a Dutch datetime
in it and for Access (OleDb)
http://www.vb-tips.com/default.aspx?ID=550279ec-6767-44ff-aaa3-eb8b44af0137

In fact is that all.

(The datetimepicker.value returns a datetime field).

Cor
 
Jerome said:
Hallo,
I know a lot has already been told about date/time fields in a
database but still confuses me, specif when dealing with
SQLserver(Express).
It seems that sqlserver only accepts the date in a "yyyyMMdd"
format? (difference between Express and MSDE2000A ?)
What is the one and only true way to deal with this problem in
VB2005: Local settings are Dutch (Belgium) ; thus date is in
"dd/MM/yy" (or perhaps dd/MM/yyyy) and time in "hh:mm:ss"

dim MyDateVar as string, MyIdVar as Integer
dim MyCommand = New Sqlcommand("",Connection)
MyCommand.commandtext = "Update MyTable Set MyDatefield = '" &
MyDateVar & "' Where MyIdField = " & MyIdVar = SomeIntegerValue
MyCommand.executenonquery

How to deal with the MyDateVar when:
Click to expand...

Store date/time values *always* in variables of type DateTime.
1.
The Variable comes from a textbox knowing that the user puts in
dd/MMyyyy In this case there is no need to have the time with it.
Click to expand...

I guess this is "dd/MM/yyyy"?

Use Date.ParseExact (or Date.Parse) to convert form string to DateTime.
2.
The date comes from a datetimepicker control
(MyDateVar = DtPicker.Value)?
Click to expand...

Declare MyDateVar as DateTime and everything is fine. Maybe you have to cut
off the time:

MyDateVar = DtPicker.Value.Date

3.
The date and time comes from the system
MyDateVar= Format(DateTime.Now, "yyyyMMdd") seems to work but
Format(DateTime.Now, "yyyyMMdd.hhmmss") gives a runtime error
Click to expand...

You don't need this for an SQL.
So, any help and/or suggestion on this will be greatly appreciated.
Click to expand...

Use parameters with MyCommand. You won't have to care about the format
anymore:

dim MyDateVar as datetime
dim MyCommand = New Sqlcommand("",Connection)

MyDateVar = date.parse(mytextbox.text)
- or -
MyDateVar = DtPicker.Value.Date
MyCommand.commandtext = "Update MyTable Set MyDatefield = @mydate Where
MyIdField = @id"

with mycommand.parameters
.add("@mydate", SqlDbType.DateTime).value = mydatevar
.add("@id", SqlDbType.Int).value = SomeIntegerValue
end with

MyCommand.executenonquery


In addition, see the T-SQL reference:
http://msdn.microsoft.com/library/en-us/tsqlref/ts_ua-uz_82n9.asp

....leading you to:
http://msdn.microsoft.com/library/en-us/tsqlref/ts_ea-ez_4aur.asp

....leading you to:
http://msdn.microsoft.com/library/en-us/tsqlref/ts_ca-co_1n1v.asp
(see "datetime constants")


Armin
 
Hi
This is my little guide :
- For dates, use date variables, not into strings. That way you can
do arithmetics, format properly , passs parameters without problems etc
- Use Cdate when picking up dates/times from text fields
- When calling SQLprocedures, use parameters.
- When building an SQL string in a (VB) program, use date format
yyyy-mm-dd , ie. today is 2006-01-25 , and format explicitly ,
do not rely on implicit (locale dependent) formatting.
ie. SQLtext = ... & format (date_var,"yyyy-mm-dd hh:MM:ss") & ...
The somewhat exotic format does not matter inside a program, the important
thing
is that SQLserver never fails to understand you correctly.
No more lottery if 01/04/06 is April 1st or January 4th or ...
Matti
 
I'm a database person, so from a database perspective:

Make sure the variable in the client is date datatype, not string.
Pass it though a command object and a parameter object to SQL Server = you are safe. ADO will do the
string conversion for you.
If you absolutely want to pass it as a string to SQL Server, read
http://www.karaszi.com/SQLServer/info_datetime.asp
 
Hoi Friends,
Thanks very much the answers. At least these are short, understandable and
valuable answers! Far more better than all the microsoft stuff readings.
I will try the suggestions right away when my sqlserverExpress is working
again. Yesterday Mr. Murphy came to visit and ruined my VS2005 and
Sqlexpress. Nice!
Anyway, the answers leaves my with one more question:
What are the benifits of using Parameters instead plain variables (for
numeric or charachter fields at least)?
As i can see at a first glance there is a lot more wrtiting to do for the
Parameters. (adding them to a command before they are usable, defining the
number of chars for a string param, etc,etc)?
For instance: If the client decides that a stringfield should have more
characters capacity, one should go trough the whole project and adjust the
number of chars for the Params that points to that specific field? Or can
one program a param with, let's say 100 chars, where the field is only 50
chars ? The max charachters is limited by the maxlength property of the
textbox anyway.

Thanks once again for the answers and suggestions
Jerome
 
What are the benifits of using Parameters instead plain variables (for numeric or charachter
fields at least)?
Click to expand...

* Avoid "SQL Injection" (Google and you will find.)

* Assuming that ADO.NET is smart enough to execute your code using sp_executesql and make parameters
for that out of your ADO.NET parameters: You will have a lot greater chance for your query plan to
be re-used.
If you just build a string and first search for "johnson", then SQL Server can cache that plan. But
that cached plan is identified (basically) based on all the text in the query. "johnson" is a part
of that text. Next time, you search for "smith", and SQL Server first searches for a plan match.
Such doesn't exists (you searched for "johnson" last time). So a new plan will be added to plan
cache for this query with "smith" embedded. I've seen installations with 10,000 instances of plans
in cache for the same query! And how much memory is now available for caching data? Not to speak
about the overhead of searching through many many thousands of plans in cache in order to find a
match - every time you execute a query - in vain. If they were parametized, then you'd have only one
plan for the query in cache, and SQL Server would substitute the parameters.

* Better yet, use stored procedures. This way you also have control over if this plan should be
cached in the first place and also plan recompiles. Along with bunch of other advantages of using
stored procedures.

*"Feels better"

I bet others can jump in with other advantages.
 
Hoi Tibor,
That explains a lot.
SQL Injection, in my case, is unlikely to occur.They are not going to tamper
with the application. There are a maximu of 4 persons working with the
application and the whole bunch is not even connected to the internet.
Nobody at the site in question ever heard about Sql not to speak about
running a query! I was obliged to use an existing MsAccess Db as backend
(they already are working for years with Access) ;-) and i had to enhance
and expanding the application. So, rewriting 200+ functions!?
Now i'm trying for myself and for learning purposes to rebuild parts of the
applic in VB2005 and with a sqlexpress as backend and that's when i ran into
those date problems. Perhaps that explains a bit more my questions and i am
happy that people like you and others are willing to give advice. If you
have to learn it from the books of Microsoft.....pfff. Even for a simple
readonly lookup table and a combobox they lead trough a complete
strongly-typed dataset! Ridicolous

Anyway, thanks a lot for the feedback
Jerome
 
Back
Top