date/time fields

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
JeromeJerome,
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/default.asp?url=/library/en-us/cpref/html/frlrfsystemglobalizationdatetimeformatinfoclasstopic.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|||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
"Jerome" <Jommeke@.fake.com> wrote in message
news:ywKBf.209108$DX6.7008400@.phobos.telenet-ops.be...
> 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
>|||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
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Jerome" <Jommeke@.fake.com> wrote in message news:ywKBf.209108$DX6.7008400@.phobos.telenet-ops.be...
> 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
>|||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
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> schreef
in bericht news:eapIArdIGHA.2900@.TK2MSFTNGP14.phx.gbl...
> 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
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Jerome" <Jommeke@.fake.com> wrote in message
> news:ywKBf.209108$DX6.7008400@.phobos.telenet-ops.be...
>> 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
>>
>|||> What are the benifits of using Parameters instead plain variables (for numeric or charachter
> fields at least)?
* 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.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Jerome" <Jommeke@.fake.com> wrote in message news:oK2Cf.211227$Xy5.6942778@.phobos.telenet-ops.be...
> 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
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> schreef in bericht
> news:eapIArdIGHA.2900@.TK2MSFTNGP14.phx.gbl...
>> 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
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Jerome" <Jommeke@.fake.com> wrote in message
>> news:ywKBf.209108$DX6.7008400@.phobos.telenet-ops.be...
>> 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
>>
>|||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
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> schreef
in bericht news:eFrJeZnIGHA.1728@.TK2MSFTNGP09.phx.gbl...
>> What are the benifits of using Parameters instead plain variables (for
>> numeric or charachter fields at least)?
> * 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.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Jerome" <Jommeke@.fake.com> wrote in message
> news:oK2Cf.211227$Xy5.6942778@.phobos.telenet-ops.be...
>> 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
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
>> schreef in bericht news:eapIArdIGHA.2900@.TK2MSFTNGP14.phx.gbl...
>> 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
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Jerome" <Jommeke@.fake.com> wrote in message
>> news:ywKBf.209108$DX6.7008400@.phobos.telenet-ops.be...
>> 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
>>
>>
>