Retrieving a datetime with a time of midnight (from a typical datetime)

Nothing difficult, I just need a way to generate a new datetime column based on the column [PostedDate], datetime. So basically I want to truncate the time. Thanks a lot.

A frequent method used is to (1) convert it to varchar using CONVERT with the 101 flavor and then (2) re-convert it back to datetime. Here are some examples:

Code Snippet

select convert(datetime, convert(varchar, getdate(), 101))
as dateOnly
/*
dateOnly
2007-09-07 00:00:00.000
*/

select dateadd(day, datediff (day, 0, getdate()), 0)
as dateOnly
/*
dateOnly
2007-09-07 00:00:00.000
*/

select cast(floor(cast(getdate() as float)) as datetime)
as dateOnly
/*
dateOnly
2007-09-07 00:00:00.000
*/

|||

Another way:

Code Snippet

select dateadd(d, datediff(d,0,[PostedDate]),0)

|||I used the dateadd method both of you suggested and it worked perfectly. Thank you very much.

retrieve the primary keys with SQL DMO and vb.net

Hello,
I am using SQL DMO with VB6, my tool has to generate TSQL Statement INSERT
and UPDATE, INSERT is ok but for an update statement, i have to retreive
the list of primary keys on a table.
Do you know which method to implement to do so ?
Thanks for your help
Olivier
Each Table object has a Keys collection. Each Key has a Type property.
Failing that, you can use T-SQL:
select
*
from
INFORMATION_SCHEMA.KEY_COLUMN_USAGE
where
1 in (
objectproperty (object_id (CONSTRAINT_NAME), 'CnstIsClustKey')
, objectproperty (object_id (CONSTRAINT_NAME), 'CnstIsNonclustKey')
)
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"oLiVieR" <ocheneson@.hotmail.com> wrote in message
news:OMUoOMCyFHA.3556@.TK2MSFTNGP12.phx.gbl...
Hello,
I am using SQL DMO with VB6, my tool has to generate TSQL Statement INSERT
and UPDATE, INSERT is ok but for an update statement, i have to retreive
the list of primary keys on a table.
Do you know which method to implement to do so ?
Thanks for your help
Olivier

retrieve the GUID for inserted record

I am using this code to insert a record in my table where i have assigned a guid datatype field to generate an automatic guid for each record. but now i need to retrieve the guid to use it to send a confirmation email to the user.

SqlConnection sql_connection = new SqlConnection("Server=xxx.xxx.xx.xxx;uid=xxxxxxxx;password=xxxxxxx;database=xxxxxxx;");
SqlCommand sql_command = new SqlCommand("INSERT INTO members (member_sex, member_cpr, member_nationality, member_block, member_gov, member_daaera, member_email, member_mobile, member_created_ip) Values (@.member_sex, @.member_cpr, @.member_nationality, @.member_block, @.member_gov, @.member_daaera, @.member_email, @.member_mobile, @.member_created_ip)", sql_connection);

sql_command.Parameters.Add(new SqlParameter("@.member_sex", Session["member_sex"].ToString()));
sql_command.Parameters.Add(new SqlParameter("@.member_cpr", Session["member_cpr"]));
sql_command.Parameters.Add(new SqlParameter("@.member_nationality", Session["member_nationality"].ToString()));
sql_command.Parameters.Add(new SqlParameter("@.member_block", Session["member_block"].ToString()));
sql_command.Parameters.Add(new SqlParameter("@.member_gov", "GOV"));
sql_command.Parameters.Add(new SqlParameter("@.member_daaera", 6));
sql_command.Parameters.Add(new SqlParameter("@.member_email", Session["member_email"].ToString().ToLower()));
sql_command.Parameters.Add(new SqlParameter("@.member_mobile", Session["member_mobile"].ToString()));
sql_command.Parameters.Add(new SqlParameter("@.member_created_ip", Request.UserHostAddress.ToString()));

sql_connection.Open();
sql_command.ExecuteNonQuery();
sql_connection.Close();

Create the GUID yourself, and pass it into the insert.

Retrieve Dataset using RS Web Service?

I am using the RS web service to generate reports. I know it is possible to
generate lists of available reports, together with a list of required
parameters for each. But is it possible to get a list of valid values for
each parameter via the web service? i.e. retrieve the dataset results that
are used to poulate the filter when the report is viewed using Report
Manager. This would save me writing a query to populate a list in my custom
front end.Dave Morrow wrote:
<snip>
> But is it possible to get a list of valid values for
> each parameter via the web service? i.e. retrieve the dataset results that
> are used to poulate the filter when the report is viewed using Report
> Manager. This would save me writing a query to populate a list in my custom
> front end.
You can check to see if a parameter has ValidValuesQueryBased specified,
and then build a combobox from the ValidValues collection of the
ReportParameter object. I've written a small report launcher in C#
which does this and it works well.
-BA|||<snip>
> and then build a combobox from the ValidValues collection of the
> ReportParameter object.
<snip>
Edit: ValidValues is a property of the ReportParameter class, and is of
type ValidValue[].
-BA|||Thanks for your help, Brian. Much appreciated.
"Brian Almond" wrote:
> <snip>
> > and then build a combobox from the ValidValues collection of the
> > ReportParameter object.
> <snip>
> Edit: ValidValues is a property of the ReportParameter class, and is of
> type ValidValue[].
> -BA
>