Help on Date

Hi,
I'm new to SQL.
I like to pick up the previous date. How can I do this?
I have a date field in my table with mm/dd/yy 12:00 AM. I want to do a query
to get the previous day's date. How do I convert the date to the previous
one?
For example: today is 11/18/05 12:00 AM, I like to get 11/17/05 12:00 AM
from the system date.
Please help,
Thanks,
Sarah>> I like to pick up the previous date. How can I do this?
Look up DATEADD function in SQL Server Books Online. The second argument for
this function can take negative values.
Anith|||Use DATEADD. something like this SELECT DATEADD(DAY, -1, GETDATE()) AS
PreviousDay
"SG" <sguo@.coopervision.ca> wrote in message
news:u2xfBqG7FHA.476@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I'm new to SQL.
> I like to pick up the previous date. How can I do this?
> I have a date field in my table with mm/dd/yy 12:00 AM. I want to do a
query
> to get the previous day's date. How do I convert the date to the previous
> one?
> For example: today is 11/18/05 12:00 AM, I like to get 11/17/05 12:00 AM
> from the system date.
> Please help,
> Thanks,
> Sarah
>|||Thanks so much for Tim and Anith quick response. I will give a try, good
start.
Appreicate
sarah
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:%23WV1JrG7FHA.3976@.TK2MSFTNGP15.phx.gbl...
> Look up DATEADD function in SQL Server Books Online. The second argument
> for this function can take negative values.
> --
> Anith
>|||Hi,
I was in a view design, how can I convert GETDATE() TO day of the month,
like 1-31.
is there a function that I can use like MONTH() etc.
Thanks,
Sarah
"SG" <sguo@.coopervision.ca> wrote in message
news:u2xfBqG7FHA.476@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I'm new to SQL.
> I like to pick up the previous date. How can I do this?
> I have a date field in my table with mm/dd/yy 12:00 AM. I want to do a
> query to get the previous day's date. How do I convert the date to the
> previous one?
> For example: today is 11/18/05 12:00 AM, I like to get 11/17/05 12:00 AM
> from the system date.
> Please help,
> Thanks,
> Sarah
>|||Use DATEPART
"SG" <sguo@.coopervision.ca> wrote in message
news:uFyK$fH7FHA.632@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I was in a view design, how can I convert GETDATE() TO day of the month,
> like 1-31.
> is there a function that I can use like MONTH() etc.
> Thanks,
> Sarah
> "SG" <sguo@.coopervision.ca> wrote in message
> news:u2xfBqG7FHA.476@.TK2MSFTNGP15.phx.gbl...
>|||Try using the DATEPART function.
"SG" wrote:

> Hi,
> I was in a view design, how can I convert GETDATE() TO day of the month,
> like 1-31.
> is there a function that I can use like MONTH() etc.
> Thanks,
> Sarah
> "SG" <sguo@.coopervision.ca> wrote in message
> news:u2xfBqG7FHA.476@.TK2MSFTNGP15.phx.gbl...
>
>|||Thanks, that works.
"Devers" <Devers@.discussions.microsoft.com> wrote in message
news:86461C90-7674-4F8B-8B70-A195C546218C@.microsoft.com...
> Try using the DATEPART function.
> "SG" wrote:
>|||Hi Everyone,
Actually DATEADD() works for me. Thanks everyone for your help.
Appreciate it and have a good day,
Sarah
"SG" <sguo@.coopervision.ca> wrote in message
news:uFyK$fH7FHA.632@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I was in a view design, how can I convert GETDATE() TO day of the month,
> like 1-31.
> is there a function that I can use like MONTH() etc.
> Thanks,
> Sarah
> "SG" <sguo@.coopervision.ca> wrote in message
> news:u2xfBqG7FHA.476@.TK2MSFTNGP15.phx.gbl...
>

Help Me Please!! Im Having Dts Troubles!!

Can anybody help me with this?

I'm trying to update three fields in one table from an import table. Is the anything you see missing in my code:

Dim rs1, strSQL

strSQL = "Select safety_valve_cap, hydro_date, hydro_psi, state_No From HBC_ZImport"
set rs1 = objConn.Execute(strSQL)

Do While NOT rs1.EOF

strSQL = "Update HBC_Boiler_Inspection set Safety_Valve_Cap = '" & rs1 ("Safety_Valve_Cap") & "', "
strSQL = strSQL & " Hydro_PSI = '" & rs1 ("Hydro_PSI") & "', "
strSQL = strSQL & " Hydro_Date = '" & rs1 ("Hydro_Date") & "' "
strSQL = strSQL & " where Boiler_ID = (Select ID from HBC_Boiler where State_No = ' " & rs1 ( "State_No") & " ') & " ' "
objConn.Execute(strSQL)

rs1.MoveNext()
Loop

Function Main()
Main = DTSTaskExecResult_Success
End FunctionSome things I noted:

1. Move the Function Main() statement to the top
2. Dim objConn
3. Instantiate objConn and rs1
4. Set the connection string for objConn
5. Open objConn

I did not see anything glaringly wrong with the SQL, but I did not focus on that.

The rewritten code would look like:

Function Main()

Dim rs1, strSQL, objConn

set objConn = CreateObject("ADODB.Connection")
set rs1 = CreateObject("ADODB.RecordSet")

objConn.ConnectionString = {insert your connection string here}
objConn.Open

strSQL = "Select safety_valve_cap, hydro_date, hydro_psi, state_No From HBC_ZImport"
set rs1 = objConn.Execute(strSQL)

Do While NOT rs1.EOF

strSQL = "Update HBC_Boiler_Inspection set Safety_Valve_Cap = '" & rs1 ("Safety_Valve_Cap") & "', "
strSQL = strSQL & " Hydro_PSI = '" & rs1 ("Hydro_PSI") & "', "
strSQL = strSQL & " Hydro_Date = '" & rs1 ("Hydro_Date") & "' "
strSQL = strSQL & " where Boiler_ID = (Select ID from HBC_Boiler where State_No = ' " & rs1 ( "State_No") & " ') & " ' "
objConn.Execute(strSQL)

rs1.MoveNext

Loop

Main = DTSTaskExecResult_Success

End Function

HTH,

Hugh Scott

Originally posted by Bigced_21
Can anybody help me with this?

I'm trying to update three fields in one table from an import table. Is the anything you see missing in my code:

Dim rs1, strSQL

strSQL = "Select safety_valve_cap, hydro_date, hydro_psi, state_No From HBC_ZImport"
set rs1 = objConn.Execute(strSQL)

Do While NOT rs1.EOF

strSQL = "Update HBC_Boiler_Inspection set Safety_Valve_Cap = '" & rs1 ("Safety_Valve_Cap") & "', "
strSQL = strSQL & " Hydro_PSI = '" & rs1 ("Hydro_PSI") & "', "
strSQL = strSQL & " Hydro_Date = '" & rs1 ("Hydro_Date") & "' "
strSQL = strSQL & " where Boiler_ID = (Select ID from HBC_Boiler where State_No = ' " & rs1 ( "State_No") & " ') & " ' "
objConn.Execute(strSQL)

rs1.MoveNext()
Loop

Function Main()
Main = DTSTaskExecResult_Success
End Function