|
 |
asp_web_howto thread: smalldatetime
Message #1 by Jean Halstad <J_Halstad@S...> on Wed, 20 Feb 2002 10:01:28 -0000
|
|
In order to deal with international and US date formats, am attempting to
convert dates to DateSerial for insertion in an SQL table. The field is
smalldatetime. This is the error I am getting.
The conversion of CHAR to SMALLDATETIME resulted in a SMALLDATETIME value
out of range.
This is the code:
strTravelDate = DateSerial(strTravelYear, strTravelMonth, strTravelDay)
'I used response.write to check the correct month is returned.
strSQL= "insert Travel (travelDate)" &_
" values ('"&strTravelDate&"')"
***************************************************************************
This e-mail and any files transmitted with it are confidential and intended
solely for the use of the individual or entity to whom they are addressed
If you have received this e-mail in error please notify
seafish@s...
If the content is not about the business of the Sea Fish Industry Authority
or the sea fish industry then the message is neither from nor sanctioned
by the Sea Fish Industry Authority.
Message #2 by Oleg Kapeljushnik <c-oleg.kapeljushnik@w...> on Wed, 20 Feb 2002 09:36:04 -0500
|
|
Check you date before you inserting.
It can be a case that date appears in American standard as "1/31/2002" but
DB configured differently.
or vice versa.
Out of range in this case means month 31 doesn't exist.
Try to insert date like "1/1/2002" or even "12/12/2002" in this case you
shouldn't get
this error.
Oleg.
-----Original Message-----
From: Jean Halstad [mailto:J_Halstad@S...]
Sent: February 20, 2002 5:01 AM
To: ASP Web HowTo
Subject: [asp_web_howto] smalldatetime
In order to deal with international and US date formats, am attempting to
convert dates to DateSerial for insertion in an SQL table. The field is
smalldatetime. This is the error I am getting.
The conversion of CHAR to SMALLDATETIME resulted in a SMALLDATETIME value
out of range.
This is the code:
strTravelDate = DateSerial(strTravelYear, strTravelMonth, strTravelDay)
'I used response.write to check the correct month is returned.
strSQL= "insert Travel (travelDate)" &_
" values ('"&strTravelDate&"')"
***************************************************************************
This e-mail and any files transmitted with it are confidential and intended
solely for the use of the individual or entity to whom they are addressed
If you have received this e-mail in error please notify
seafish@s...
If the content is not about the business of the Sea Fish Industry Authority
or the sea fish industry then the message is neither from nor sanctioned
by the Sea Fish Industry Authority.
$subst('Email.Unsub').
Message #3 by "Ken Schaefer" <ken@a...> on Thu, 21 Feb 2002 12:55:25 +1100
|
|
Format the date as a string literal which happens to have the date in ISO
format: yyyy/mm/dd
That way there is no confusion over what the date actually is.
This function:
www.adopenstatic.com/resources/code/formatdate.asp
will do it for you.
Cheers
Ken
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "Jean Halstad" <J_Halstad@S...>
Subject: [asp_web_howto] smalldatetime
: In order to deal with international and US date formats, am attempting to
: convert dates to DateSerial for insertion in an SQL table. The field is
: smalldatetime. This is the error I am getting.
:
: The conversion of CHAR to SMALLDATETIME resulted in a SMALLDATETIME value
: out of range.
:
: This is the code:
:
: strTravelDate = DateSerial(strTravelYear, strTravelMonth, strTravelDay)
: 'I used response.write to check the correct month is returned.
:
: strSQL= "insert Travel (travelDate)" &_
: " values ('"&strTravelDate&"')"
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Message #4 by Jean Halstad <J_Halstad@S...> on Thu, 21 Feb 2002 09:00:51 -0000
|
|
I thought using DateSerial was supposed to eliminate the international/US
formatting problem.
-----Original Message-----
From: Oleg Kapeljushnik [mailto:c-oleg.kapeljushnik@w...]
Sent: 20 February 2002 14:36
To: ASP Web HowTo
Subject: [asp_web_howto] RE: smalldatetime
Check you date before you inserting.
It can be a case that date appears in American standard as "1/31/2002" but
DB configured differently.
or vice versa.
Out of range in this case means month 31 doesn't exist.
Try to insert date like "1/1/2002" or even "12/12/2002" in this case you
shouldn't get
this error.
Oleg.
-----Original Message-----
From: Jean Halstad [mailto:J_Halstad@S...]
Sent: February 20, 2002 5:01 AM
To: ASP Web HowTo
Subject: [asp_web_howto] smalldatetime
In order to deal with international and US date formats, am attempting to
convert dates to DateSerial for insertion in an SQL table. The field is
smalldatetime. This is the error I am getting.
The conversion of CHAR to SMALLDATETIME resulted in a SMALLDATETIME value
out of range.
This is the code:
strTravelDate = DateSerial(strTravelYear, strTravelMonth, strTravelDay)
'I used response.write to check the correct month is returned.
strSQL= "insert Travel (travelDate)" &_
" values ('"&strTravelDate&"')"
***************************************************************************
This e-mail and any files transmitted with it are confidential and intended
solely for the use of the individual or entity to whom they are addressed
If you have received this e-mail in error please notify
seafish@s...
If the content is not about the business of the Sea Fish Industry Authority
or the sea fish industry then the message is neither from nor sanctioned
by the Sea Fish Industry Authority.
$subst('Email.Unsub').
$subst('Email.Unsub').
***************************************************************************
This e-mail and any files transmitted with it are confidential and intended
solely for the use of the individual or entity to whom they are addressed
If you have received this e-mail in error please notify
seafish@s...
If the content is not about the business of the Sea Fish Industry Authority
or the sea fish industry then the message is neither from nor sanctioned
by the Sea Fish Industry Authority.
Message #5 by Jean Halstad <J_Halstad@S...> on Thu, 21 Feb 2002 11:25:54 -0000
|
|
Brilliant! I've been struggling with this for weeks, and the solution is so
b***** simple. Am puzzled to see that the date actually appears in the SQL
DB table as dd/mm/yy.
-----Original Message-----
From: Ken Schaefer [mailto:ken@a...]
Sent: 21 February 2002 01:55
To: ASP Web HowTo
Subject: [asp_web_howto] Re: smalldatetime
Format the date as a string literal which happens to have the date in ISO
format: yyyy/mm/dd
That way there is no confusion over what the date actually is.
This function:
www.adopenstatic.com/resources/code/formatdate.asp
will do it for you.
Cheers
Ken
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "Jean Halstad" <J_Halstad@S...>
Subject: [asp_web_howto] smalldatetime
: In order to deal with international and US date formats, am attempting to
: convert dates to DateSerial for insertion in an SQL table. The field is
: smalldatetime. This is the error I am getting.
:
: The conversion of CHAR to SMALLDATETIME resulted in a SMALLDATETIME value
: out of range.
:
: This is the code:
:
: strTravelDate = DateSerial(strTravelYear, strTravelMonth, strTravelDay)
: 'I used response.write to check the correct month is returned.
:
: strSQL= "insert Travel (travelDate)" &_
: " values ('"&strTravelDate&"')"
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
$subst('Email.Unsub').
***************************************************************************
This e-mail and any files transmitted with it are confidential and intended
solely for the use of the individual or entity to whom they are addressed
If you have received this e-mail in error please notify
seafish@s...
If the content is not about the business of the Sea Fish Industry Authority
or the sea fish industry then the message is neither from nor sanctioned
by the Sea Fish Industry Authority.
Message #6 by "Ken Schaefer" <ken@a...> on Fri, 22 Feb 2002 10:30:50 +1100
|
|
It just means that you have a variant of subtype Date in your VBScript.
When you use an SQL statement though you are just sending a string of
literal text to the DB for processing. The DB isn't aware of any of your
VBScript datatypes. It just looks at the literal text that you are using for
your date, and then decides that it isn't in a valid date format.
Use ISO date format and you'll be right.
Cheers
Ken
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "Jean Halstad" <J_Halstad@S...>
Subject: [asp_web_howto] RE: smalldatetime
: I thought using DateSerial was supposed to eliminate the international/US
: formatting problem.
:
: -----Original Message-----
: From: Oleg Kapeljushnik [mailto:c-oleg.kapeljushnik@w...]
: Sent: 20 February 2002 14:36
: To: ASP Web HowTo
: Subject: [asp_web_howto] RE: smalldatetime
:
:
: Check you date before you inserting.
: It can be a case that date appears in American standard as "1/31/2002" but
: DB configured differently.
: or vice versa.
: Out of range in this case means month 31 doesn't exist.
: Try to insert date like "1/1/2002" or even "12/12/2002" in this case you
: shouldn't get
: this error.
:
: Oleg.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Message #7 by "Ken Schaefer" <ken@a...> on Fri, 22 Feb 2002 10:34:38 +1100
|
|
The date is stored in SQL Server as a number: the integer portion reflects
the number of days since an arbitrary starting date (eg 1/1/1900), and the
decimal part indicates the portion of the day that has passed, eg:
1.75
would be equivalent to: 2nd January 1900 6:00 PM
How SQL Server *displays* this when you open a table in EM, or run a query
in QA is dependant on your regional settings.
Cheers
Ken
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "Jean Halstad" <J_Halstad@S...>
Subject: [asp_web_howto] Re: smalldatetime
: Brilliant! I've been struggling with this for weeks, and the solution is
so
: b***** simple. Am puzzled to see that the date actually appears in the SQL
: DB table as dd/mm/yy.
:
: -----Original Message-----
: From: Ken Schaefer [mailto:ken@a...]
: Sent: 21 February 2002 01:55
: To: ASP Web HowTo
: Subject: [asp_web_howto] Re: smalldatetime
:
:
: Format the date as a string literal which happens to have the date in ISO
: format: yyyy/mm/dd
: That way there is no confusion over what the date actually is.
:
: This function:
: www.adopenstatic.com/resources/code/formatdate.asp
: will do it for you.
:
: Cheers
: Ken
:
: ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
: From: "Jean Halstad" <J_Halstad@S...>
: Subject: [asp_web_howto] smalldatetime
:
:
: : In order to deal with international and US date formats, am attempting
to
: : convert dates to DateSerial for insertion in an SQL table. The field is
: : smalldatetime. This is the error I am getting.
: :
: : The conversion of CHAR to SMALLDATETIME resulted in a SMALLDATETIME
value
: : out of range.
: :
: : This is the code:
: :
: : strTravelDate = DateSerial(strTravelYear, strTravelMonth, strTravelDay)
: : 'I used response.write to check the correct month is returned.
: :
: : strSQL= "insert Travel (travelDate)" &_
: : " values ('"&strTravelDate&"')"
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Message #8 by Jean Halstad <J_Halstad@S...> on Tue, 26 Feb 2002 14:43:57 -0000
|
|
Oh dear, here we go again. Formatted a date to insert into a DB:
strTravelDate=strYear & "/" & strMonth & "/" & strDay, which works fine.
When I construct the date in the same way to query the database, the query
does not find the date in the DB.
strSQL = "select * from Travel where Based = '"& strBased &"' and " &_
" TravelDate between '"&strTravelDate&"' and '"&strTravelDatePlus&"'" &_
" and returnDate = '"&strReturnDate&"'" &_
" order by LastName, TravelDate"
rs.open strSQL, cn, 1, 3
-----Original Message-----
From: Ken Schaefer [mailto:ken@a...]
Sent: 21 February 2002 23:35
To: ASP Web HowTo
Subject: [asp_web_howto] Re: smalldatetime
The date is stored in SQL Server as a number: the integer portion reflects
the number of days since an arbitrary starting date (eg 1/1/1900), and the
decimal part indicates the portion of the day that has passed, eg:
1.75
would be equivalent to: 2nd January 1900 6:00 PM
How SQL Server *displays* this when you open a table in EM, or run a query
in QA is dependant on your regional settings.
Cheers
Ken
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "Jean Halstad" <J_Halstad@S...>
Subject: [asp_web_howto] Re: smalldatetime
: Brilliant! I've been struggling with this for weeks, and the solution is
so
: b***** simple. Am puzzled to see that the date actually appears in the SQL
: DB table as dd/mm/yy.
:
: -----Original Message-----
: From: Ken Schaefer [mailto:ken@a...]
: Sent: 21 February 2002 01:55
: To: ASP Web HowTo
: Subject: [asp_web_howto] Re: smalldatetime
:
:
: Format the date as a string literal which happens to have the date in ISO
: format: yyyy/mm/dd
: That way there is no confusion over what the date actually is.
:
: This function:
: www.adopenstatic.com/resources/code/formatdate.asp
: will do it for you.
:
: Cheers
: Ken
:
: ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
: From: "Jean Halstad" <J_Halstad@S...>
: Subject: [asp_web_howto] smalldatetime
:
:
: : In order to deal with international and US date formats, am attempting
to
: : convert dates to DateSerial for insertion in an SQL table. The field is
: : smalldatetime. This is the error I am getting.
: :
: : The conversion of CHAR to SMALLDATETIME resulted in a SMALLDATETIME
value
: : out of range.
: :
: : This is the code:
: :
: : strTravelDate = DateSerial(strTravelYear, strTravelMonth, strTravelDay)
: : 'I used response.write to check the correct month is returned.
: :
: : strSQL= "insert Travel (travelDate)" &_
: : " values ('"&strTravelDate&"')"
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
$subst('Email.Unsub').
***************************************************************************
This e-mail and any files transmitted with it are confidential and intended
solely for the use of the individual or entity to whom they are addressed
If you have received this e-mail in error please notify
seafish@s...
If the content is not about the business of the Sea Fish Industry Authority
or the sea fish industry then the message is neither from nor sanctioned
by the Sea Fish Industry Authority.
Message #9 by "Ken Schaefer" <ken@a...> on Wed, 27 Feb 2002 12:48:41 +1100
|
|
Hi:
Do this:
<%
Response.Write(strSQL)
Response.End
%>
You should now have your SQL statement on your screen. Open Query Analyser
and cut-n-paste the query into the QA window and run the query there. Does
it return any results?
Cheers
Ken
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "Jean Halstad" <J_Halstad@S...>
Subject: [asp_web_howto] Re: smalldatetime
: Oh dear, here we go again. Formatted a date to insert into a DB:
:
: strTravelDate=strYear & "/" & strMonth & "/" & strDay, which works fine.
:
: When I construct the date in the same way to query the database, the query
: does not find the date in the DB.
:
: strSQL = "select * from Travel where Based = '"& strBased &"' and " &_
: " TravelDate between '"&strTravelDate&"' and '"&strTravelDatePlus&"'" &_
: " and returnDate = '"&strReturnDate&"'" &_
: " order by LastName, TravelDate"
: rs.open strSQL, cn, 1, 3
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Message #10 by Jean Halstad <J_Halstad@S...> on Thu, 28 Feb 2002 09:58:30 -0000
|
|
Thanks for that. Had forgotten all about doing that. Couldn't find anything
wrong with the query, but as I stared at it, it suddenly dawned on me that I
was asking the wrong question!
Still, I have solved it now and reckon I know a lot more about the dates
demon than I used to.
Hi:
Do this:
<%
Response.Write(strSQL)
Response.End
%>
You should now have your SQL statement on your screen. Open Query Analyser
and cut-n-paste the query into the QA window and run the query there. Does
it return any results?
Cheers
Ken
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "Jean Halstad" <J_Halstad@S...>
Subject: [asp_web_howto] Re: smalldatetime
: Oh dear, here we go again. Formatted a date to insert into a DB:
:
: strTravelDate=strYear & "/" & strMonth & "/" & strDay, which works fine.
:
: When I construct the date in the same way to query the database, the query
: does not find the date in the DB.
:
: strSQL = "select * from Travel where Based = '"& strBased &"' and " &_
: " TravelDate between '"&strTravelDate&"' and '"&strTravelDatePlus&"'" &_
: " and returnDate = '"&strReturnDate&"'" &_
: " order by LastName, TravelDate"
: rs.open strSQL, cn, 1, 3
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
$subst('Email.Unsub').
***************************************************************************
This e-mail and any files transmitted with it are confidential and intended
solely for the use of the individual or entity to whom they are addressed
If you have received this e-mail in error please notify
seafish@s...
If the content is not about the business of the Sea Fish Industry Authority
or the sea fish industry then the message is neither from nor sanctioned
by the Sea Fish Industry Authority.
|
|
|
View Cart
