Help Converting Character string to smalldatetime data type

I am attempting to pass a string variable from a form to an SQL statement an
d
I get this error "Syntax error converting character string to smalldatetime
data type"
Your assistance is appreciated in advance....
Here is my code
<%
Dim TESTTHIS__MMColParam1
TESTTHIS__MMColParam1 = "0"
If (Request.Form("StartDate") <> "") Then
TESTTHIS__MMColParam1 = Request.Form("StartDate")
End If
%>
<%
Dim TESTTHIS__MMColParam2
TESTTHIS__MMColParam2 = "0"
If (Request.Form("EndDate") <> "") Then
TESTTHIS__MMColParam2 = Request.Form("EndDate")
End If
%>
<%
Dim TESTTHIS
Dim TESTTHIS_numRows
Set TESTTHIS = Server.CreateObject("ADODB.Recordset")
TESTTHIS.ActiveConnection = MM_CSSMetricsCONN_STRING
TESTTHIS.Source = "SELECT Team, SumofInAdherenceSecsQty /
(SumofInAdherenceSecsQty + SumofOutOfAdherenceSecsQty) AS Adherence FROM
(SELECT TOP 1000 Team, SUM(OutOfAdherenceSecsQty) AS
SumofOutOfAdherenceSecsQty, SUM(InAdherenceSecsQty) AS
SumofInAdherenceSecsQty FROM bo.[National Call Stats] WHERE (Site =
'Dallas') AND ([Date] >='" + Replace(TESTTHIS__MMColParam1, "'", "''") + "')
AND ([Date] <= '" + Replace(TESTTHIS__MMColParam2, "'", "''") + "')
GROUP BY Team, Site ORDER BY Team, Site) DERIVEDTBL"
TESTTHIS.CursorType = 0
TESTTHIS.CursorLocation = 2
TESTTHIS.LockType = 1
TESTTHIS.Open()
TESTTHIS_numRows = 0
%>
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200606/1Run a Profiler trace to see the actual select statement being sent to SQL
Sever. Also, consider using parameters instead of concatenating values.
Parameters are more secure and you don't have to double-up embedded quotes.
Hope this helps.
Dan Guzman
SQL Server MVP
"Chamark via webservertalk.com" <u21870@.uwe> wrote in message
news:6168f7c85cf47@.uwe...
>I am attempting to pass a string variable from a form to an SQL statement
>and
> I get this error "Syntax error converting character string to
> smalldatetime
> data type"
> Your assistance is appreciated in advance....
> Here is my code
> <%
> Dim TESTTHIS__MMColParam1
> TESTTHIS__MMColParam1 = "0"
> If (Request.Form("StartDate") <> "") Then
> TESTTHIS__MMColParam1 = Request.Form("StartDate")
> End If
> %>
> <%
> Dim TESTTHIS__MMColParam2
> TESTTHIS__MMColParam2 = "0"
> If (Request.Form("EndDate") <> "") Then
> TESTTHIS__MMColParam2 = Request.Form("EndDate")
> End If
> %>
> <%
> Dim TESTTHIS
> Dim TESTTHIS_numRows
> Set TESTTHIS = Server.CreateObject("ADODB.Recordset")
> TESTTHIS.ActiveConnection = MM_CSSMetricsCONN_STRING
> TESTTHIS.Source = "SELECT Team, SumofInAdherenceSecsQty /
> (SumofInAdherenceSecsQty + SumofOutOfAdherenceSecsQty) AS Adherence FROM
> (SELECT TOP 1000 Team, SUM(OutOfAdherenceSecsQty) AS
> SumofOutOfAdherenceSecsQty, SUM(InAdherenceSecsQty) AS
> SumofInAdherenceSecsQty FROM bo.[National Call Stats] WHERE (Site =
> 'Dallas') AND ([Date] >='" + Replace(TESTTHIS__MMColParam1, "'", "''") +
> "')
> AND ([Date] <= '" + Replace(TESTTHIS__MMColParam2, "'", "''") + "')
> GROUP BY Team, Site ORDER BY Team, Site)
> DERIVEDTBL"
> TESTTHIS.CursorType = 0
> TESTTHIS.CursorLocation = 2
> TESTTHIS.LockType = 1
> TESTTHIS.Open()
> TESTTHIS_numRows = 0
> %>
> --
> Message posted via webservertalk.com
> http://www.webservertalk.com/Uwe/Forum...amming/200606/1|||Also keep in mind that if something is a valid date it still might not
be able to be converted into smalldatetime (for example dates before
19000101 and after 20790606)
Denis the SQL Menace
http://sqlservercode.blogspot.com/
Chamark via webservertalk.com wrote:
> I am attempting to pass a string variable from a form to an SQL statement
and
> I get this error "Syntax error converting character string to smalldatetim
e
> data type"
> Your assistance is appreciated in advance....
> Here is my code
> <%
> Dim TESTTHIS__MMColParam1
> TESTTHIS__MMColParam1 = "0"
> If (Request.Form("StartDate") <> "") Then
> TESTTHIS__MMColParam1 = Request.Form("StartDate")
> End If
> %>
> <%
> Dim TESTTHIS__MMColParam2
> TESTTHIS__MMColParam2 = "0"
> If (Request.Form("EndDate") <> "") Then
> TESTTHIS__MMColParam2 = Request.Form("EndDate")
> End If
> %>
> <%
> Dim TESTTHIS
> Dim TESTTHIS_numRows
> Set TESTTHIS = Server.CreateObject("ADODB.Recordset")
> TESTTHIS.ActiveConnection = MM_CSSMetricsCONN_STRING
> TESTTHIS.Source = "SELECT Team, SumofInAdherenceSecsQty /
> (SumofInAdherenceSecsQty + SumofOutOfAdherenceSecsQty) AS Adherence FROM
> (SELECT TOP 1000 Team, SUM(OutOfAdherenceSecsQty) AS
> SumofOutOfAdherenceSecsQty, SUM(InAdherenceSecsQty) AS
> SumofInAdherenceSecsQty FROM bo.[National Call Stats] WHERE (Site =
> 'Dallas') AND ([Date] >='" + Replace(TESTTHIS__MMColParam1, "'", "''") + "')
> AND ([Date] <= '" + Replace(TESTTHIS__MMColParam2, "'", "''") + "')
> GROUP BY Team, Site ORDER BY Team, Site) DERIVEDTB
L"
> TESTTHIS.CursorType = 0
> TESTTHIS.CursorLocation = 2
> TESTTHIS.LockType = 1
> TESTTHIS.Open()
> TESTTHIS_numRows = 0
> %>
> --
> Message posted via webservertalk.com
> http://www.webservertalk.com/Uwe/Forum...amming/200606/1|||I am able to see that it is getting to the server because I get a return of
the column headers but I am not getting any data. When I hard code the dates
in the query works fine. I am sending for example 8/1/2005 from the form. If
I take out the two / and send 812005 I get an overflow error?
Dan Guzman wrote:
>Run a Profiler trace to see the actual select statement being sent to SQL
>Sever. Also, consider using parameters instead of concatenating values.
>Parameters are more secure and you don't have to double-up embedded quotes.
>
>[quoted text clipped - 43 lines]
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200606/1|||On Wed, 07 Jun 2006 20:12:46 GMT, "Chamark via webservertalk.com"
<u21870@.uwe> wrote:

>I am able to see that it is getting to the server because I get a return of
>the column headers but I am not getting any data. When I hard code the date
s
>in the query works fine. I am sending for example 8/1/2005 from the form. I
f
>I take out the two / and send 812005 I get an overflow error?
You will get an error, as '812005' does not convert to datetime:
select convert(datetime,'812005')
Server: Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in
an out-of-range datetime value.
The most reliable format for a date string is YYYYMMDD:
select convert(datetime,'20050801')
--
2005-08-01 00:00:00.000
Roy Harvey
Beacon Falls, CTsql

Help - Just using the time of DateTime or SmallDateTime

I need to store the time in a table ("10:00:00 AM") and then compare just
the time of the CURRENT_TIMESTAMP to it programmatically in T-SQL. I know
this is simple as heck but because I am a newbie I am stumbling. Can
someone provide me with a sample of this? Thank you.What datatype is the column in the table of?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Code Boy" <CodeBoy@.microsoft.com> wrote in message news:e%23jVzKNVGHA.5092@.TK2MSFTNGP10.ph
x.gbl...
>I need to store the time in a table ("10:00:00 AM") and then compare just
> the time of the CURRENT_TIMESTAMP to it programmatically in T-SQL. I know
> this is simple as heck but because I am a newbie I am stumbling. Can
> someone provide me with a sample of this? Thank you.
>|||That is up to me (so whatever you tell me). I just want to end up selecting
(with T-SQL) any row where a column has a time only greater then the time
portion of the current time stamp. Thank you.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OMb8aRNVGHA.4740@.TK2MSFTNGP14.phx.gbl...
> What datatype is the column in the table of?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Code Boy" <CodeBoy@.microsoft.com> wrote in message
> news:e%23jVzKNVGHA.5092@.TK2MSFTNGP10.phx.gbl...|||code
select convert(varchar(8),current_timestamp,108
) , it will give you
time.
You have to compare it with time in your table.
Look at convert function for more details in BOL.
Regards
Amish Shah.|||When I do this the compare to the time in my table does not select anyting.
It is as if now that they are varchars the >= operator does not work
properly. I will look in BOL for more info. Thank you.
"amish" <shahamishm@.gmail.com> wrote in message
news:1143820529.897848.99710@.g10g2000cwb.googlegroups.com...
> code
> select convert(varchar(8),current_timestamp,108
) , it will give you
> time.
> You have to compare it with time in your table.
> Look at convert function for more details in BOL.
> Regards
> Amish Shah.
>|||>I need to store the time in a table ("10:00:00 AM") and then compare just
>the time of the CURRENT_TIMESTAMP to it programmatically in T-SQL.
Okay, so when you insert the data into SQL Server, make sure the date is not
included (or manually force it to be 1900-01-01 on insert).
Now, you can compare to CURRENT_TIMESTAMP - DATEDIFF(DAY, 0, GETDATE()). An
example:
CREATE TABLE #foo
(
FooID INT,
NextBar SMALLDATETIME
)
INSERT #foo SELECT 1, '1900-01-01 18:34';
INSERT #foo SELECT 2, '02:25'; -- 1900-01-01 is the default
INSERT #foo SELECT 3, '06:34';
INSERT #foo SELECT 4, '10:25';
INSERT #foo SELECT 5, '14:57';
INSERT #foo SELECT 6, '20:36';
INSERT #foo SELECT 6, '23:11';
-- now let's find those rows where NextBar is within the next 6 hours
DECLARE @.s SMALLDATETIME, @.e SMALLDATETIME;
SET @.s = CURRENT_TIMESTAMP - DATEDIFF(DAY, 0, GETDATE());
SET @.e = DATEADD(HOUR, 6, @.s);
SELECT FooID, NextBar = CONVERT(CHAR(5), NextBar, 108)
FROM #foo
WHERE NextBar >= @.s
AND NextBar <= @.e;
-- see all rows:
SELECT FooID, NextBar = CONVERT(CHAR(5), NextBar, 108)
FROM #foo;
DROP TABLE #foo;|||Figured it out:
select cast(convert(varchar,OurTime,114) as datetime) as OurTime
from FileName
where cast(convert(varchar,OurTime,114) as datetime) >
convert(varchar,getdate(),114)
Thank you all!
"Code Boy" <CodeBoy@.microsoft.com> wrote in message
news:e%23jVzKNVGHA.5092@.TK2MSFTNGP10.phx.gbl...
>I need to store the time in a table ("10:00:00 AM") and then compare just
>the time of the CURRENT_TIMESTAMP to it programmatically in T-SQL. I know
>this is simple as heck but because I am a newbie I am stumbling. Can
>someone provide me with a sample of this? Thank you.
>|||Figured it out:
select cast(convert(varchar,OurTime,114) as datetime) as OurTime
from FileName
where cast(convert(varchar,OurTime,114) as datetime) >
convert(varchar,getdate(),114)
Thank you all!
"Code Boy" <CodeBoy@.microsoft.com> wrote in message
news:e%23jVzKNVGHA.5092@.TK2MSFTNGP10.phx.gbl...
>I need to store the time in a table ("10:00:00 AM") and then compare just
>the time of the CURRENT_TIMESTAMP to it programmatically in T-SQL. I know
>this is simple as heck but because I am a newbie I am stumbling. Can
>someone provide me with a sample of this? Thank you.
>|||> select cast(convert(varchar,OurTime,114) as datetime) as OurTime
> from FileName
> where cast(convert(varchar,OurTime,114) as datetime) >
> convert(varchar,getdate(),114)
All these casts and converts will really hurt your performance.
If you're cunning enough to have an index on that column, you should compare
the plan for this solution compared to the one I provided...