convert date variable to Datetime

Hei

unread,

Jan 24, 2004, 7:53:06 AM1/24/04

to

i can't insert a Date value from vb.net to MSDE,
do i need convert the data to fit database field?

(the data type in database is DateTime, and i just want to store date value
only)

thx.
Hei.

Ken Tucker [MVP]

unread,

Jan 24, 2004, 9:33:31 AM1/24/04

to

Hi,

Format the datetime data when you display it to the user.

http://www.syncfusion.com/FAQ/WinForms/FAQ_c44c.asp#q867q
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemwindowsformsbindingclassformattopic.asp

Ken
------------------
"Hei" <chun...@msn.com> wrote in message
news:%23a$RJkn4D...@TK2MSFTNGP12.phx.gbl...

Aaron Bertrand [MVP]

unread,

Jan 24, 2004, 2:28:02 PM1/24/04

to

> i can't insert a Date value from vb.net to MSDE,

What does "can't" mean? Do you get an error? If so, what is it? What code
are you using?

> (the data type in database is DateTime, and i just want to store date
value
> only)

You can't store just the date. You can insert the date only, but SQL Server
will add the time component.

http://www.aspfaq.com/2206

If you pass just a date, this time component will be midnight. So, when
retrieving, you can use CONVERT to return a formatted "date only" or else
use formatting functions at the client to remove the time from the result.

--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/

Hei

unread,

Jan 25, 2004, 1:54:09 AM1/25/04

to

the error catch by Tyr,Catch as follow.

system.date.sqlclient.sqlexception: the conversion of a char data type to
datetime data type resulted in an out -of-range datetime value.

the statement has been terminated.
at system.data.sqlclient.sqlcommand.executeNonQuery()
.....
;

sqlstatement: INSERT INTO Prescription(PrescriptionDate)VALUES ('25/1/2004
0:00:00')

"Aaron Bertrand [MVP]" <aa...@TRASHaspfaq.com> wrote in message
news:eHwDwAr4...@TK2MSFTNGP09.phx.gbl...

Steven Licciardi

unread,

Jan 25, 2004, 2:23:28 AM1/25/04

to

You can convert you chars to a DateTime object, then insert your DateTime
object, so
Dim str As String = "12/12/12"

Dim dte As Date = Convert.ToDateTime(str)

Insert dte.

Steven

"Hei" <chun...@msn.com> wrote in message

news:uEkpNAx...@TK2MSFTNGP11.phx.gbl...

Hei

unread,

Jan 25, 2004, 3:06:33 AM1/25/04

to

it is no error on run but the value inserted to database incorrect.
the actually value is 25/1/2004, the database is record 1/1/1900.

"Steven Licciardi" <steven_licciar...@hotmail.com> wrote in
message news:uAeRgQx...@TK2MSFTNGP11.phx.gbl...

Ken Tucker [MVP]

unread,

Jan 25, 2004, 7:00:16 AM1/25/04

to

Hi,

Try adding the date in the month/day/year format. 1/25/2004

Ken
---------------

"Hei" <chun...@msn.com> wrote in message

news:OLvFjox4...@TK2MSFTNGP12.phx.gbl...

Aaron Bertrand [MVP]

unread,

Jan 25, 2004, 7:02:21 AM1/25/04

to

> sqlstatement: INSERT INTO Prescription(PrescriptionDate)VALUES ('25/1/2004
> 0:00:00')

Don't use that format! Blecch!

INSERT INTO Prescription(PrescriptionDate)VALUES ('20040125')

Hei

unread,

Jan 25, 2004, 11:28:47 AM1/25/04

to

same...

"Ken Tucker [MVP]" <vb...@bellsouth.net> wrote in message
news:%23HdcPrz...@TK2MSFTNGP12.phx.gbl...

Hei

unread,

Jan 25, 2004, 11:31:53 AM1/25/04

to

thx. is ok right now. but how to change a Date data type to this format?

"Aaron Bertrand [MVP]" <aa...@TRASHaspfaq.com> wrote in message

news:ORpTXsz4...@TK2MSFTNGP12.phx.gbl...

Aaron Bertrand [MVP]

unread,

Jan 25, 2004, 1:05:17 PM1/25/04

to

An important point I think you're missing is that a DATETIME datatype
doesn't have such a format. Its internal storage is not equivalent to its
display representation. So, if you have a datetime column, run that INSERT
statement, and then run a SELECT and you'll see that it worked just fine.

--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/

"Hei" <chun...@msn.com> wrote in message

news:#Y2n7C24...@tk2msftngp13.phx.gbl...

Joe "Nuke Me Xemu" Foster

unread,

Jan 25, 2004, 1:07:13 PM1/25/04

to

"Aaron Bertrand [MVP]" <aa...@TRASHaspfaq.com> wrote in message <news:ORpTXsz4...@TK2MSFTNGP12.phx.gbl>...

> > sqlstatement: INSERT INTO Prescription(PrescriptionDate)VALUES ('25/1/2004
> > 0:00:00')
>
> Don't use that format! Blecch!
>
> INSERT INTO Prescription(PrescriptionDate)VALUES ('20040125')

Did you misspell '2004-01-25', or are you crusading against the
date/time data type altogether?

In VB, the Format$ string might be "yyyy\-mm\-dd hh\:nn\:ss" .

--
Joe Foster <mailto:jlfoster%40znet.com> Sign the Check! <http://www.xenu.net/>
WARNING: I cannot be held responsible for the above They're coming to
because my cats have apparently learned to type. take me away, ha ha!

Aaron Bertrand [MVP]

unread,

Jan 25, 2004, 1:20:18 PM1/25/04

to

> > INSERT INTO Prescription(PrescriptionDate)VALUES ('20040125')
>
> Did you misspell '2004-01-25', or are you crusading against the
> date/time data type altogether?

No, I did not misspell anything. YYYY-MM-DD is not a safe ISO standard
format in SQL Server (regardless of what Format$ uses in VB, which is
largely irrelevant here anyway). Try this script:

set language french
select 'yours', dateadd(day, 1, '2004-01-25')
select 'mine', dateadd(day, 1, '20040125')
go
select isdate('2004-01-25')
select isdate('20040125')

set language english
select 'yours', dateadd(day, 1, '2004-01-25')
select 'mine', dateadd(day, 1, '20040125')
go
select isdate('2004-01-25')
select isdate('20040125')

Steven Licciardi

unread,

Jan 26, 2004, 2:46:17 AM1/26/04

to

How about 2004/1/25 ?

I think you can get datetime confusions when operating systems are set up
different to what the user expects. US or UK format for instance. But I've
never tested that so I'm not sure.

Steven

"Hei" <chun...@msn.com> wrote in message

news:umQBNB24...@TK2MSFTNGP12.phx.gbl...

Chris Barber

unread,

Jan 26, 2004, 4:32:47 AM1/26/04

to

YYYY-MM-DD is not a safe ISO standard

</snippet>

In what manner can an ISO date (or datetime) be mis-construed by any system
to be other then that intended. After all, that *is* the purpose of the ISO
format.

The following works fine:

set language french

select 'mine', dateadd(day, 1, '20040125')
go

select isdate('20040125')

set language italian

select 'mine', dateadd(day, 1, '20040125')
go

select isdate('20040125')

set language german

select 'mine', dateadd(day, 1, '20040125')
go

select isdate('20040125')

set language english

select 'mine', dateadd(day, 1, '20040125')
go

select isdate('20040125')

The only issue I can see is that SQL Server 2000 doesn't like the '-' in
non-English language settings but the dates without the '-' worked fine in
all languages that I have tested.

Perhaps I misread the intent of your post?

Chris.

Greg Low (MVP)

unread,

Jan 26, 2004, 5:30:06 AM1/26/04

to

No, the one that Aaron suggested 'YYYYMMDD' is the only one we use for sql
server. Strongly recommended.

HTH,

--
Greg Low (MVP)
MSDE Manager SQL Tools
www.whitebearconsulting.com

"Steven Licciardi" <steven_licciar...@hotmail.com> wrote in

message news:Oo$78B%234DH...@TK2MSFTNGP11.phx.gbl...

Aaron Bertrand [MVP]

unread,

Jan 26, 2004, 7:05:47 AM1/26/04

to

YYYY-MM-DD is not a safe ISO standard to be used in SQL Server. If you go
back and see my example, and read over your post, you'll see that you
confirmed exactly why it should not be used.

--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/

"Chris Barber" <ch...@blue-canoe.co.uk.NOSPAM> wrote in message
news:ehzmh9#4DHA...@TK2MSFTNGP12.phx.gbl...

Chris Barber

unread,

Jan 26, 2004, 7:38:28 AM1/26/04

to

OK - sorry for my confusion.

I must admit that I wasn't aware of the '-' issue with SQL Server in
non-English form - I'll have to change my own scripts to fix that.

Thanks.

Chris.

"Aaron Bertrand [MVP]" <aa...@TRASHaspfaq.com> wrote in message

news:ONGA9SA5...@TK2MSFTNGP09.phx.gbl...