HELP with DateAdd statement and SQL

I am trying to pull the date from the day before "objDate =
DateAdd("d", -1, Date)" in standard time format, and I am wanting to
convert it into UTC time "dtmCurrentDate = "&objDate&" " then pass it
into a SQL command " "WHERE [Type]='error' AND
[TimeWritten]>'&dtmTargetDate&' ". The script works however it isn't
passing the date and coverting into UTC. Can someone please help me
with this? Below you will find my script.

Thanks,

Robnson

-----------------------------------------------------------------------------
dim Conn, objRS, objConn, strSQL, objDate, dtmTargetDate
objDate = DateAdd("d", -1, Date)
strComputer = "."
Set objSWbemServices = GetObject("winmgmts:" _
& "{impersonationLevel=impersonate}!\\" & strComputer &
"\root\cimv2")
Set colTimeZone = objSWbemServices.ExecQuery _
("SELECT * FROM Win32_TimeZone")
For Each objTimeZone in colTimeZone
strBias = objTimeZone.Bias
Next

dtmCurrentDate = "&objDate&"
dtmTargetDate = Year(dtmCurrentDate)

dtmMonth = Month(dtmCurrentDate)
If Len(dtmMonth) = 1 Then
dtmMonth = "0" & dtmMonth
End If

dtmTargetDate = dtmTargetDate & dtmMonth

dtmDay = Day(dtmCurrentDate)
If Len(dtmDay) = 1 Then
dtmDay = "0" & dtmDay
End If

dtmTargetDate = dtmTargetDate & dtmDay & "000000.000000"
dtmTargetDate = dtmTargetDate & Cstr(strBias)

Set Conn = CreateObject("ADODB.Connection")
Set objRS = CreateObject("ADODB.Recordset")
Conn.Open = "provider=sqloledb;data source=SCK-EAV-01;" & _
"user id=sa;password=justD0!t;" & _
"initial catalog=Eventlogs"



strSQL = "SELECT [ComputerName], [EventCode], [Message] " & _
"[SourceName], [TimeWritten], [Type] FROM [EventTable] " & _
"WHERE [Type]='error' AND [TimeWritten]>'&dtmTargetDate&' ORDER BY
[TimeWritten]"

objRS.Open strSQL, Conn, 0, 1 'adOpenForwardOnly, adLockReadOnly

objRS.MoveFirst
While Not objRS.EOF
WScript.Echo objRS("ComputerName") & objRS("EventCode") &
objRS("SourceName")
objRS.MoveNext
Wend
______________________________________________________________________

Bob
Mon Mar 01 03:57:10 CST 2004

robnson wrote:
> I am trying to pull the date from the day before "objDate =
> DateAdd("d", -1, Date)" in standard time format, and I am wanting to
> convert it into UTC time "dtmCurrentDate = "&objDate&" " then pass it
> into a SQL command " "WHERE [Type]='error' AND
> [TimeWritten]>'&dtmTargetDate&' ". The script works however it isn't
> passing the date and coverting into UTC. Can someone please help me
> with this? Below you will find my script.
>

What is the datatype of the TimeWritten column? If it is datetime, you are
making a mistake by passing the the time in UTC format.


> Conn.Open = "provider=sqloledb;data source=SCK-EAV-01;" & _
> "user id=sa;password=justD0!t;" & _

Gasp! You have two major security problems here:
1. Using the sa account for applications is a major gaffe. You should create
a login with the minimum permissions required to perform the tasks required
for your application and use that account.
2. The first gaffe is relatively minor compared to this one: You just
revealed your server's sa password to the internet!!! Protect your server's
sa account as if your job depended on it (it probably does). Go change that
password now!


Bob Barrows

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

stephen
Mon Mar 01 13:51:34 CST 2004

Thanks, and I saw this after I posted this email. The password got
changed however this machines doesn't touch the internet, it is in a
lab environment.

Thanks,

Steve Robinson

"Bob Barrows" <reb01501@NOyahoo.SPAMcom> wrote in message news:<eg3oRO3$DHA.2308@tk2msftngp13.phx.gbl>...
> robnson wrote:
> > I am trying to pull the date from the day before "objDate =
> > DateAdd("d", -1, Date)" in standard time format, and I am wanting to
> > convert it into UTC time "dtmCurrentDate = "&objDate&" " then pass it
> > into a SQL command " "WHERE [Type]='error' AND
> > [TimeWritten]>'&dtmTargetDate&' ". The script works however it isn't
> > passing the date and coverting into UTC. Can someone please help me
> > with this? Below you will find my script.
> >
>
> What is the datatype of the TimeWritten column? If it is datetime, you are
> making a mistake by passing the the time in UTC format.
>
>
> > Conn.Open = "provider=sqloledb;data source=SCK-EAV-01;" & _
> > "user id=sa;password=justD0!t;" & _
>
> Gasp! You have two major security problems here:
> 1. Using the sa account for applications is a major gaffe. You should create
> a login with the minimum permissions required to perform the tasks required
> for your application and use that account.
> 2. The first gaffe is relatively minor compared to this one: You just
> revealed your server's sa password to the internet!!! Protect your server's
> sa account as if your job depended on it (it probably does). Go change that
> password now!
>
>
> Bob Barrows

stephen
Mon Mar 01 14:50:36 CST 2004

Thank you Bob for you help, the datatype is set to char, but the
entries comes in with a UTC formatted time stamp. So I need to change
this to something else?

I am importing event logs into a Database, and the time stamps comes
into the database as a UTC formated date and time, so I am trying to
take the current date, -1 which will be yesterday, converting that
date into a UTC formated Data and put it into my SQL Query as the
value in the table is > the converted date.

I hope that makes since.

Thanks,

Robnson
"Bob Barrows" <reb01501@NOyahoo.SPAMcom> wrote in message news:<eg3oRO3$DHA.2308@tk2msftngp13.phx.gbl>...
> robnson wrote:
> > I am trying to pull the date from the day before "objDate =
> > DateAdd("d", -1, Date)" in standard time format, and I am wanting to
> > convert it into UTC time "dtmCurrentDate = "&objDate&" " then pass it
> > into a SQL command " "WHERE [Type]='error' AND
> > [TimeWritten]>'&dtmTargetDate&' ". The script works however it isn't
> > passing the date and coverting into UTC. Can someone please help me
> > with this? Below you will find my script.
> >
>
> What is the datatype of the TimeWritten column? If it is datetime, you are
> making a mistake by passing the the time in UTC format.
>
>
> > Conn.Open = "provider=sqloledb;data source=SCK-EAV-01;" & _
> > "user id=sa;password=justD0!t;" & _
>
> Gasp! You have two major security problems here:
> 1. Using the sa account for applications is a major gaffe. You should create
> a login with the minimum permissions required to perform the tasks required
> for your application and use that account.
> 2. The first gaffe is relatively minor compared to this one: You just
> revealed your server's sa password to the internet!!! Protect your server's
> sa account as if your job depended on it (it probably does). Go change that
> password now!
>
>
> Bob Barrows

Bob
Mon Mar 01 15:11:22 CST 2004

robnson wrote:
> Thank you Bob for you help, the datatype is set to char, but the
> entries comes in with a UTC formatted time stamp. So I need to change
> this to something else?
>
> I am importing event logs into a Database, and the time stamps comes
> into the database as a UTC formated date and time, so I am trying to
> take the current date, -1 which will be yesterday, converting that
> date into a UTC formated Data and put it into my SQL Query as the
> value in the table is > the converted date.
>
> I hope that makes since.
>

It makes sense, but it is going to be very difficult to achieve: you cannot
do datetime comparisons with character data without converting the character
data to datetime. While this may be possible, it will greatly deteriorate
the performance of any query that you run agains the table, since the
conversion function will need to be applied to every row in your table.

I strongly suggest that you create a new column with datatype datetime and
run an update query to set it equal to the date and time stored in your
TimeWritten column.

Do you need help with converting the UTC entries to datetime? If so, show me
an example of one of the UTC entries and I'll show you how to do it.

Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

stephen
Tue Mar 02 11:18:59 CST 2004

Bob,

Thanks, for you help. Yes, I will need help with converting the time,
and help with adding that to my original script. Here is a sample of
my record in the TimeWritten field. ( 20040122164949.000000-480
)without the (). Also I am new to SQL, but you said just do a update
query and have a new column equal to the TimeWritten column? Again
thank you for all the help.

Steve Robinson

"Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message news:<uVK1KH9$DHA.220@TK2MSFTNGP09.phx.gbl>...
> robnson wrote:
> > Thank you Bob for you help, the datatype is set to char, but the
> > entries comes in with a UTC formatted time stamp. So I need to change
> > this to something else?
> >
> > I am importing event logs into a Database, and the time stamps comes
> > into the database as a UTC formated date and time, so I am trying to
> > take the current date, -1 which will be yesterday, converting that
> > date into a UTC formated Data and put it into my SQL Query as the
> > value in the table is > the converted date.
> >
> > I hope that makes since.
> >
>
> It makes sense, but it is going to be very difficult to achieve: you cannot
> do datetime comparisons with character data without converting the character
> data to datetime. While this may be possible, it will greatly deteriorate
> the performance of any query that you run agains the table, since the
> conversion function will need to be applied to every row in your table.
>
> I strongly suggest that you create a new column with datatype datetime and
> run an update query to set it equal to the date and time stored in your
> TimeWritten column.
>
> Do you need help with converting the UTC entries to datetime? If so, show me
> an example of one of the UTC entries and I'll show you how to do it.
>
> Bob Barrows

Bob
Tue Mar 02 12:58:10 CST 2004

robnson wrote:
> Bob,
>
> Thanks, for you help. Yes, I will need help with converting the time,
> and help with adding that to my original script. Here is a sample of
> my record in the TimeWritten field. ( 20040122164949.000000-480
> )without the ().

We need to make a decision.
Option 1: store the date and time, (this part: 20040122164949.000000) in a
datetime column, and the offset in another column

Option 2: use the offset to transform the time to GMT and store the
resulting time and date in a datetime column

I think Option 2 is more appropriate for this application and will use it
for this example. You may be able to modify my code if you think Option 1 is
more appropriate. I am going to assume the fractional portion of the second
is not significant. The following script, which can be run in Query
Analyzer, illustrates how to do the conversion.

set @utcdate = '20040122164949.000000-480'

select

convert(datetime,
left(@utcdate,8) + ' ' +
SUBSTRING(@utcdate,9,2) + ':' +
SUBSTRING(@utcdate,11,2) + ':' +
SUBSTRING(@utcdate,13,2)) Original,

dateadd(n,
-cast(RIGHT(@utcdate,len(@utcdate) - 21) as int),
convert(datetime,
left(@utcdate,8) + ' ' +
SUBSTRING(@utcdate,9,2) + ':' +
SUBSTRING(@utcdate,11,2) + ':' +
SUBSTRING(@utcdate,13,2))
) GMT

> Also I am new to SQL, but you said just do a update
> query and have a new column equal to the TimeWritten column? Again
> thank you for all the help.
>

OK, add a new column to your table - call it TimeWrittenGMT. You can use
this script:

ALTER TABLE <table name> ADD TimeWrittenGMT datetime NULL

And here's how to write the UPDATE query to put data into the column:

UPDATE <table name>
SET TimeWrittenGMT =
dateadd(n,
-cast(RIGHT(TimeWritten,len(TimeWritten) - 21) as int),
convert(datetime,
left(TimeWritten,8) + ' ' +
SUBSTRING(TimeWritten,9,2) + ':' +
SUBSTRING(TimeWritten,11,2) + ':' +
SUBSTRING(TimeWritten,13,2))
)

In ASP, use DateAdd to convert the local time to GMT before passing it to
the query, in which you use TimeWrittenGMT in the WHERE clause instead of
TimeWritten.

Bob Barrows

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Dr
Tue Mar 02 16:07:55 CST 2004

JRS: In article <3af55c40.0403020918.33380aa0@posting.google.com>, seen
in news:microsoft.public.scripting.vbscript, robnson
<stephen@ncsquare.com> posted at Tue, 2 Mar 2004 09:18:59 :-

> Yes, I will need help with converting the time,
>and help with adding that to my original script. Here is a sample of
>my record in the TimeWritten field. ( 20040122164949.000000-480
>)without the ().

S = "20040122164949.000000-480"
T = _
DateSerial(Mid(S, 1, 4), Mid(S, 5, 2), Mid(S, 7, 2)) + _
TimeSerial(Mid(S, 9, 2), Mid(S, 11, 2), Mid(S, 13, 2)) + _
Cdbl("0"&Mid(S, 15, 7))/86400

document.write T + Cdbl(Mid(S, 22, 5))/1440

You may not require the /1440 term, or may need to change its sign;
check field widths.

--
© John Stockton, Surrey, UK. ?@merlyn.demon.co.uk Turnpike v4.00 IE 4 ©
<URL:http://jibbering.com/faq/> Jim Ley's FAQ for news:comp.lang.javascript
<URL:http://www.merlyn.demon.co.uk/js-index.htm> jscr maths, dates, sources.
<URL:http://www.merlyn.demon.co.uk/> TP/BP/Delphi/jscr/&c, FAQ items, links.

Al
Mon Mar 01 22:46:18 CST 2004


"robnson" <stephen@ncsquare.com> wrote in message
news:3af55c40.0403011151.3870057b@posting.google.com...
> Thanks, and I saw this after I posted this email. The password got
> changed however this machines doesn't touch the internet, it is in a
> lab environment.

Granted. But do you have staff in your lab environment that are not
authorized to know that password yet who are known to touch the internet
from home? The issue is more the revealing of a critical password than the
forum in which this was done.

/Al


> Thanks,
>
> Steve Robinson
>
> "Bob Barrows" <reb01501@NOyahoo.SPAMcom> wrote in message
news:<eg3oRO3$DHA.2308@tk2msftngp13.phx.gbl>...
> > robnson wrote:
> > > I am trying to pull the date from the day before "objDate =
> > > DateAdd("d", -1, Date)" in standard time format, and I am wanting to
> > > convert it into UTC time "dtmCurrentDate = "&objDate&" " then pass it
> > > into a SQL command " "WHERE [Type]='error' AND
> > > [TimeWritten]>'&dtmTargetDate&' ". The script works however it isn't
> > > passing the date and coverting into UTC. Can someone please help me
> > > with this? Below you will find my script.
> > >
> >
> > What is the datatype of the TimeWritten column? If it is datetime, you
are
> > making a mistake by passing the the time in UTC format.
> >
> >
> > > Conn.Open = "provider=sqloledb;data source=SCK-EAV-01;" & _
> > > "user id=sa;password=justD0!t;" & _
> >
> > Gasp! You have two major security problems here:
> > 1. Using the sa account for applications is a major gaffe. You should
create
> > a login with the minimum permissions required to perform the tasks
required
> > for your application and use that account.
> > 2. The first gaffe is relatively minor compared to this one: You just
> > revealed your server's sa password to the internet!!! Protect your
server's
> > sa account as if your job depended on it (it probably does). Go change
that
> > password now!
> >
> >
> > Bob Barrows

stephen
Wed Mar 03 01:27:59 CST 2004

I tired everthing you told me but I am still getting an mismatch and
when I put in a actually Date to get coverted, it gives me a error
saying SQL that it is having a problem converting the charater set.
So to go over everyting, I need to first get a date, then convert that
into a UTC time format, then pass that to SQL at the Date?

Thanks,

Steve Robinson

"Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message news:<uVK1KH9$DHA.220@TK2MSFTNGP09.phx.gbl>...
> robnson wrote:
> > Thank you Bob for you help, the datatype is set to char, but the
> > entries comes in with a UTC formatted time stamp. So I need to change
> > this to something else?
> >
> > I am importing event logs into a Database, and the time stamps comes
> > into the database as a UTC formated date and time, so I am trying to
> > take the current date, -1 which will be yesterday, converting that
> > date into a UTC formated Data and put it into my SQL Query as the
> > value in the table is > the converted date.
> >
> > I hope that makes since.
> >
>
> It makes sense, but it is going to be very difficult to achieve: you cannot
> do datetime comparisons with character data without converting the character
> data to datetime. While this may be possible, it will greatly deteriorate
> the performance of any query that you run agains the table, since the
> conversion function will need to be applied to every row in your table.
>
> I strongly suggest that you create a new column with datatype datetime and
> run an update query to set it equal to the date and time stored in your
> TimeWritten column.
>
> Do you need help with converting the UTC entries to datetime? If so, show me
> an example of one of the UTC entries and I'll show you how to do it.
>
> Bob Barrows

Bob
Wed Mar 03 08:44:41 CST 2004

robnson wrote:
> I tired everthing you told me but I am still getting an mismatch and
> when I put in a actually Date to get coverted, it gives me a error
> saying SQL that it is having a problem converting the charater set.
> So to go over everyting, I need to first get a date, then convert that
> into a UTC time format, then pass that to SQL at the Date?

No. Again, you will not be able to do datetime comaparisons with character
data. In ASP, you get a date, use DateAdd() with the offset to convert it to
GMT, and use that date to compare with the dates in the TimeWrittenGMT
column.


Please show me what you've got now so I can reproduce your problem. Show me
a couple of rows of data from your table. Just a few of the columns - make
sure you show the data from both the TimeWritten and TimeWrittenGMT columns,
as well as one or two of the other columns. You will need to show me the
datatypes of the columns that you show me.

Then show me the snippet of vbscript code you used to produce your mismatch.

If you will be adding more data to the table, you will need to use a trigger
to make sure the TimeWrittenGMT column gets populated. Alternatively, with
SQL2000, you can use a calculated column.

Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

stephen
Thu Mar 04 00:55:36 CST 2004

Thank you again, here is my Code
-------------------------------------------------------------------------------
dim Conn, objRS, objConn, strSQL, objDate, dtmTargetDate
objDate = DateAdd("d", -1, Date)
strComputer = "."
Set objSWbemServices = GetObject("winmgmts:" _
& "{impersonationLevel=impersonate}!\\" & strComputer &
"\root\cimv2")
Set colTimeZone = objSWbemServices.ExecQuery _
("SELECT * FROM Win32_TimeZone")
For Each objTimeZone in colTimeZone
strBias = objTimeZone.Bias
Next

dtmCurrentDate = "&objDate&"
dtmTargetDate = Year(dtmCurrentDate)

dtmMonth = Month(dtmCurrentDate)
If Len(dtmMonth) = 1 Then
dtmMonth = "0" & dtmMonth
End If

dtmTargetDate = dtmTargetDate & dtmMonth

dtmDay = Day(dtmCurrentDate)
If Len(dtmDay) = 1 Then
dtmDay = "0" & dtmDay
End If

dtmTargetDate = dtmTargetDate & dtmDay & "000000.000000"
dtmTargetDate = dtmTargetDate & Cstr(strBias)

Set Conn = CreateObject("ADODB.Connection")
Set objRS = CreateObject("ADODB.Recordset")
Conn.Open = "provider=sqloledb;data source=SCK-EAV-01;" & _
"user id=evtlogs;password=xxxxxxxx;" & _
"initial catalog=Eventlogs"



strSQL = "SELECT [ComputerName], [EventCode], [Message] " & _
"[SourceName], [TimeWritten], [TimeWrittenGMT], [Type] FROM
[EventTable] " & _
"WHERE [Type]='error' AND [TimeWrittenGMT]>'&dtmTargetDate&' ORDER BY
[TimeWritten]"

objRS.Open strSQL, Conn, 0, 1 'adOpenForwardOnly, adLockReadOnly
objRS.MoveFirst
While Not objRS.EOF
WScript.Echo objRS("ComputerName") & objRS("EventCode")
&objRS("SourceName")
objRS.MoveNext
Wend
-------------------------------------------------------------------------------
Here is the data in the columns So the below is the date how it comes
into SQL in UTC format using char datatype and the second date is the
UPdate you showed me how to do with date datatype. Sorry couldn't copy
the info because of formating.

Column Names
[Type] DataType char 'Information'

[TimeWriiten] DataType char '20040301113509.000000-480'

[TimeWrittenGMT] DataType Date '3/1/2004 11:35:09AM'

Column Names
[Type] DataType char 'Information'

[TimeWriiten] DataType char '20040301205025.000000-480'

[TimeWrittenGMT] DataType Date '3/1/2004 8:50:25 PM'


--------------------------------------------------------------------------------

Also I will need to do this conversion all the time,.

I hope this information will help you, I tried my best to get past the
formating thing. If you need the information presenting better you
can send me an email and I will reply back to you, or you can just let
me know what else you might need and I will get that for you. Thanks
for all you help, I really appreciate it. Steve Robinson



"Bob Barrows" <reb01501@NOyahoo.SPAMcom> wrote in message news:<#5#uO4SAEHA.2212@TK2MSFTNGP10.phx.gbl>...
> robnson wrote:
> > I tired everthing you told me but I am still getting an mismatch and
> > when I put in a actually Date to get coverted, it gives me a error
> > saying SQL that it is having a problem converting the charater set.
> > So to go over everyting, I need to first get a date, then convert that
> > into a UTC time format, then pass that to SQL at the Date?
>
> No. Again, you will not be able to do datetime comaparisons with character
> data. In ASP, you get a date, use DateAdd() with the offset to convert it to
> GMT, and use that date to compare with the dates in the TimeWrittenGMT
> column.
>
>
> Please show me what you've got now so I can reproduce your problem. Show me
> a couple of rows of data from your table. Just a few of the columns - make
> sure you show the data from both the TimeWritten and TimeWrittenGMT columns,
> as well as one or two of the other columns. You will need to show me the
> datatypes of the columns that you show me.
>
> Then show me the snippet of vbscript code you used to produce your mismatch.
>
> If you will be adding more data to the table, you will need to use a trigger
> to make sure the TimeWrittenGMT column gets populated. Alternatively, with
> SQL2000, you can use a calculated column.
>
> Bob Barrows