date parsing

Hello,

I have a source with two smalldatetime fields, the first field contains 8/1/2006 12:00:00 AM, and the second field contains 8/10/2006 7:57:00 PM.

I would like to have the date from the first field and the time from the second field. No chance of changing the source system to do this for me.

What I have so far works, except the time portion is converted to 19:57:00 instead of 7:57:00 p.m. Any Ideas? My expression is below.

(DT_STR,2,1252)DATEPART("month",FIELD1) + "/" + (DT_STR,2,1252)DATEPART("Day",FIELD1) + "/" + (DT_STR,4,1252)DATEPART("Year",FIELD1) + " " + (DT_STR,2,1252)DATEPART("Hour",FIELD2) + ":" + (DT_STR,2,1252)DATEPART("Minute",FIELD2) + ":" + (DT_STR,2,1252)DATEPART("SS",FIELD2)

Thanks!

Try casting the values as DT_DBTIME or DT_DBDATE.

-Jamie

|||I added a data conversion transform to convert the output from the derived column transform to a database timestamp and it appears to be working. I probably could do all of this in one transformation, but this will work for now. Thanks!|||

You could wrap the cast to DT_DBTIMESTAMP around your whole expression in the derived column to avoid using the data convert downstream.

Mark

|||Thanks. I tried that and it worked great.

date parameter

One of the fields in the table is of type smalldatetime.
When I select a date from the report parameter (a calender), the report produces an error: The value provided for the report parameter 'ValueDate' is not valid for its type.
Please note that if I use a report parameter of type string and enter 21 may 2007 the report works. But if a date is selected from the calender control i.e. 21/05/2007 the report produces the above error.

How is it possible to use the calender parameter without the report giving the error pls?
Thanks

i think u should go to report parameter ->properties -> change datatype to datetime|||

Hi,
Yes I am doing just that.
That is the problem because even though the parameter is set to datetime, it does not accept the format such as 21/05/2007 which is what you get if the date is selected from the calender.

Thanks

|||

Hi there,

Have ever you tried to fill a data in other format? This error semms like a invalid format date. Try to fill 05/21/2007 (English format - mm/dd/yyyy).

Let me know if this solved your problem.

Lant

|||you have to pass it in mm/dd/yyyy format

Date Parameter

Have a report that requires a @.StartDate parameter. This will equal a
ActualDateTime fields in a table.
I have the following code listed in my where clause
and (tvo.ActualDateTime = @.StartDate)
but keeps getting throwing an error when i test. As we are in New Zealand
our date format is dd/MM/yyyy but when entering a start date in this format i
get an "arithmetic overflow error converting expression to date type
smalldatetime". I assume this is because the database is storing the field as
a datetime and its format is MM/dd/yyyy. I have set the parameter to
datatype datetime. I know this is probably easy to sort, just need a little
assistance.
Cheers.Are you getting this error AFTER you set the parameter to datetime?
It is understandable when the parameter is string, you'd have to format it
correctly before sending it to SQL..
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Nat Johnson" <NatJohnson@.discussions.microsoft.com> wrote in message
news:9A05E7D1-8DA9-44DB-B5E7-5271DEDC3425@.microsoft.com...
> Have a report that requires a @.StartDate parameter. This will equal a
> ActualDateTime fields in a table.
> I have the following code listed in my where clause
> and (tvo.ActualDateTime = @.StartDate)
> but keeps getting throwing an error when i test. As we are in New Zealand
> our date format is dd/MM/yyyy but when entering a start date in this
> format i
> get an "arithmetic overflow error converting expression to date type
> smalldatetime". I assume this is because the database is storing the field
> as
> a datetime and its format is MM/dd/yyyy. I have set the parameter to
> datatype datetime. I know this is probably easy to sort, just need a
> little
> assistance.
> Cheers.|||Cheers Wayne
I have run the report in the preview tab without the parameter statement in
the where clause and i get data returned.
The datatype of the datetime field that I need the @.StartDate parameter to
match is of smalldatetime type.
With the @.StartDate parameter set to datetime I get data returned, no
problem there. But only if i enter the date into the parameter box as
MM/dd/yyyy. I want to be able to enter it as dd/MM/yyyy and have it display
the correct data.
hope this makes it a bit clearer.
i assume i have to convert the date time just not sure on the syntax required
"Wayne Snyder" wrote:
> Are you getting this error AFTER you set the parameter to datetime?
> It is understandable when the parameter is string, you'd have to format it
> correctly before sending it to SQL..
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Nat Johnson" <NatJohnson@.discussions.microsoft.com> wrote in message
> news:9A05E7D1-8DA9-44DB-B5E7-5271DEDC3425@.microsoft.com...
> > Have a report that requires a @.StartDate parameter. This will equal a
> > ActualDateTime fields in a table.
> >
> > I have the following code listed in my where clause
> >
> > and (tvo.ActualDateTime = @.StartDate)
> >
> > but keeps getting throwing an error when i test. As we are in New Zealand
> > our date format is dd/MM/yyyy but when entering a start date in this
> > format i
> > get an "arithmetic overflow error converting expression to date type
> > smalldatetime". I assume this is because the database is storing the field
> > as
> > a datetime and its format is MM/dd/yyyy. I have set the parameter to
> > datatype datetime. I know this is probably easy to sort, just need a
> > little
> > assistance.
> >
> > Cheers.
>
>|||Check the code of your report. The second to last line in your XML will be:
<Language>en-US</Language>
Change it to:
<Language>en-NZ</Language>
Also ensure you have SP1 at least installed.
"Nat Johnson" <NatJohnson@.discussions.microsoft.com> wrote in message
news:F5DCEDDB-4819-4C5F-BC8B-44A4CFA80163@.microsoft.com...
> Cheers Wayne
> I have run the report in the preview tab without the parameter statement
in
> the where clause and i get data returned.
> The datatype of the datetime field that I need the @.StartDate parameter to
> match is of smalldatetime type.
> With the @.StartDate parameter set to datetime I get data returned, no
> problem there. But only if i enter the date into the parameter box as
> MM/dd/yyyy. I want to be able to enter it as dd/MM/yyyy and have it
display
> the correct data.
> hope this makes it a bit clearer.
> i assume i have to convert the date time just not sure on the syntax
required
>
> "Wayne Snyder" wrote:
> > Are you getting this error AFTER you set the parameter to datetime?
> >
> > It is understandable when the parameter is string, you'd have to format
it
> > correctly before sending it to SQL..
> >
> > --
> > Wayne Snyder, MCDBA, SQL Server MVP
> > Mariner, Charlotte, NC
> > www.mariner-usa.com
> > (Please respond only to the newsgroups.)
> >
> > I support the Professional Association of SQL Server (PASS) and it's
> > community of SQL Server professionals.
> > www.sqlpass.org
> >
> > "Nat Johnson" <NatJohnson@.discussions.microsoft.com> wrote in message
> > news:9A05E7D1-8DA9-44DB-B5E7-5271DEDC3425@.microsoft.com...
> > > Have a report that requires a @.StartDate parameter. This will equal a
> > > ActualDateTime fields in a table.
> > >
> > > I have the following code listed in my where clause
> > >
> > > and (tvo.ActualDateTime = @.StartDate)
> > >
> > > but keeps getting throwing an error when i test. As we are in New
Zealand
> > > our date format is dd/MM/yyyy but when entering a start date in this
> > > format i
> > > get an "arithmetic overflow error converting expression to date type
> > > smalldatetime". I assume this is because the database is storing the
field
> > > as
> > > a datetime and its format is MM/dd/yyyy. I have set the parameter to
> > > datatype datetime. I know this is probably easy to sort, just need a
> > > little
> > > assistance.
> > >
> > > Cheers.
> >
> >
> >|||Nat,
If your parameter is set to datetime, then there is a bug in the
preview tab that doesn't translate to dd/mm/yyyy it assumes US format.
I found the solution to be in the preview tab use yyyy-mm-dd, it seems
to be a universal format for SQL. DateTime is not 'stored' in any
national format, it's just a number which gets formatted based on
locale.
You'll probably find it works OK when deployed!
Chris
AshVsAOD wrote:
> Check the code of your report. The second to last line in your XML
> will be: <Language>en-US</Language>
> Change it to:
> <Language>en-NZ</Language>
>
> Also ensure you have SP1 at least installed.
> "Nat Johnson" <NatJohnson@.discussions.microsoft.com> wrote in message
> news:F5DCEDDB-4819-4C5F-BC8B-44A4CFA80163@.microsoft.com...
> > Cheers Wayne
> >
> > I have run the report in the preview tab without the parameter
> > statement
> in
> > the where clause and i get data returned.
> >
> > The datatype of the datetime field that I need the @.StartDate
> > parameter to match is of smalldatetime type.
> >
> > With the @.StartDate parameter set to datetime I get data returned,
> > no problem there. But only if i enter the date into the parameter
> > box as MM/dd/yyyy. I want to be able to enter it as dd/MM/yyyy and
> > have it
> display
> > the correct data.
> >
> > hope this makes it a bit clearer.
> >
> > i assume i have to convert the date time just not sure on the syntax
> required
> >
> >
> >
> > "Wayne Snyder" wrote:
> >
> > > Are you getting this error AFTER you set the parameter to
> > > datetime?
> > >
> > > It is understandable when the parameter is string, you'd have to
> > > format
> it
> > > correctly before sending it to SQL..
> > >
> > > --
> > > Wayne Snyder, MCDBA, SQL Server MVP
> > > Mariner, Charlotte, NC
> > > www.mariner-usa.com
> > > (Please respond only to the newsgroups.)
> > >
> > > I support the Professional Association of SQL Server (PASS) and
> > > it's community of SQL Server professionals.
> > > www.sqlpass.org
> > >
> > > "Nat Johnson" <NatJohnson@.discussions.microsoft.com> wrote in
> > > message news:9A05E7D1-8DA9-44DB-B5E7-5271DEDC3425@.microsoft.com...
> > > > Have a report that requires a @.StartDate parameter. This will
> > > > equal a ActualDateTime fields in a table.
> > > >
> > > > I have the following code listed in my where clause
> > > >
> > > > and (tvo.ActualDateTime = @.StartDate)
> > > >
> > > > but keeps getting throwing an error when i test. As we are in
> > > > New
> Zealand
> > > > our date format is dd/MM/yyyy but when entering a start date in
> > > > this format i
> > > > get an "arithmetic overflow error converting expression to date
> > > > type smalldatetime". I assume this is because the database is
> > > > storing the
> field
> > > > as
> > > > a datetime and its format is MM/dd/yyyy. I have set the
> > > > parameter to datatype datetime. I know this is probably easy to
> > > > sort, just need a little
> > > > assistance.
> > > >
> > > > Cheers.
> > >
> > >
> > >|||Thanks Chris
and you were right...works fine once deployed. just testing at preview
doesn't show correct date format...oh well at least it works...just wish i
hadn't spent so much time trying to fix something i couldn't.
have a good day...
"Chris McGuigan" wrote:
> Nat,
> If your parameter is set to datetime, then there is a bug in the
> preview tab that doesn't translate to dd/mm/yyyy it assumes US format.
> I found the solution to be in the preview tab use yyyy-mm-dd, it seems
> to be a universal format for SQL. DateTime is not 'stored' in any
> national format, it's just a number which gets formatted based on
> locale.
> You'll probably find it works OK when deployed!
> Chris
>
> AshVsAOD wrote:
> > Check the code of your report. The second to last line in your XML
> > will be: <Language>en-US</Language>
> >
> > Change it to:
> >
> > <Language>en-NZ</Language>
> >
> >
> >
> > Also ensure you have SP1 at least installed.
> >
> > "Nat Johnson" <NatJohnson@.discussions.microsoft.com> wrote in message
> > news:F5DCEDDB-4819-4C5F-BC8B-44A4CFA80163@.microsoft.com...
> > > Cheers Wayne
> > >
> > > I have run the report in the preview tab without the parameter
> > > statement
> > in
> > > the where clause and i get data returned.
> > >
> > > The datatype of the datetime field that I need the @.StartDate
> > > parameter to match is of smalldatetime type.
> > >
> > > With the @.StartDate parameter set to datetime I get data returned,
> > > no problem there. But only if i enter the date into the parameter
> > > box as MM/dd/yyyy. I want to be able to enter it as dd/MM/yyyy and
> > > have it
> > display
> > > the correct data.
> > >
> > > hope this makes it a bit clearer.
> > >
> > > i assume i have to convert the date time just not sure on the syntax
> > required
> > >
> > >
> > >
> > > "Wayne Snyder" wrote:
> > >
> > > > Are you getting this error AFTER you set the parameter to
> > > > datetime?
> > > >
> > > > It is understandable when the parameter is string, you'd have to
> > > > format
> > it
> > > > correctly before sending it to SQL..
> > > >
> > > > --
> > > > Wayne Snyder, MCDBA, SQL Server MVP
> > > > Mariner, Charlotte, NC
> > > > www.mariner-usa.com
> > > > (Please respond only to the newsgroups.)
> > > >
> > > > I support the Professional Association of SQL Server (PASS) and
> > > > it's community of SQL Server professionals.
> > > > www.sqlpass.org
> > > >
> > > > "Nat Johnson" <NatJohnson@.discussions.microsoft.com> wrote in
> > > > message news:9A05E7D1-8DA9-44DB-B5E7-5271DEDC3425@.microsoft.com...
> > > > > Have a report that requires a @.StartDate parameter. This will
> > > > > equal a ActualDateTime fields in a table.
> > > > >
> > > > > I have the following code listed in my where clause
> > > > >
> > > > > and (tvo.ActualDateTime = @.StartDate)
> > > > >
> > > > > but keeps getting throwing an error when i test. As we are in
> > > > > New
> > Zealand
> > > > > our date format is dd/MM/yyyy but when entering a start date in
> > > > > this format i
> > > > > get an "arithmetic overflow error converting expression to date
> > > > > type smalldatetime". I assume this is because the database is
> > > > > storing the
> > field
> > > > > as
> > > > > a datetime and its format is MM/dd/yyyy. I have set the
> > > > > parameter to datatype datetime. I know this is probably easy to
> > > > > sort, just need a little
> > > > > assistance.
> > > > >
> > > > > Cheers.
> > > >
> > > >
> > > >
>|||I know what you mean! I found this out the hard way too!
If something doesn't seem right in preview, it's often worth deploying
and seeing if it's OK there. The rendering engine in Preview is not the
same as in Report Manager.
Chris
Nat Johnson wrote:
> Thanks Chris
> and you were right...works fine once deployed. just testing at
> preview doesn't show correct date format...oh well at least it
> works...just wish i hadn't spent so much time trying to fix
> something i couldn't.
> have a good day...
> "Chris McGuigan" wrote:
> > Nat,
> > If your parameter is set to datetime, then there is a bug in the
> > preview tab that doesn't translate to dd/mm/yyyy it assumes US
> > format. I found the solution to be in the preview tab use
> > yyyy-mm-dd, it seems to be a universal format for SQL. DateTime is
> > not 'stored' in any national format, it's just a number which gets
> > formatted based on locale.
> >
> > You'll probably find it works OK when deployed!
> >
> > Chris
> >
> >
> > AshVsAOD wrote:
> >
> > > Check the code of your report. The second to last line in your
> > > XML will be: <Language>en-US</Language>
> > >
> > > Change it to:
> > >
> > > <Language>en-NZ</Language>
> > >
> > >
> > >
> > > Also ensure you have SP1 at least installed.
> > >
> > > "Nat Johnson" <NatJohnson@.discussions.microsoft.com> wrote in
> > > message news:F5DCEDDB-4819-4C5F-BC8B-44A4CFA80163@.microsoft.com...
> > > > Cheers Wayne
> > > >
> > > > I have run the report in the preview tab without the parameter
> > > > statement
> > > in
> > > > the where clause and i get data returned.
> > > >
> > > > The datatype of the datetime field that I need the @.StartDate
> > > > parameter to match is of smalldatetime type.
> > > >
> > > > With the @.StartDate parameter set to datetime I get data
> > > > returned, no problem there. But only if i enter the date into
> > > > the parameter box as MM/dd/yyyy. I want to be able to enter it
> > > > as dd/MM/yyyy and have it
> > > display
> > > > the correct data.
> > > >
> > > > hope this makes it a bit clearer.
> > > >
> > > > i assume i have to convert the date time just not sure on the
> > > > syntax
> > > required
> > > >
> > > >
> > > >
> > > > "Wayne Snyder" wrote:
> > > >
> > > > > Are you getting this error AFTER you set the parameter to
> > > > > datetime?
> > > > >
> > > > > It is understandable when the parameter is string, you'd have
> > > > > to format
> > > it
> > > > > correctly before sending it to SQL..
> > > > >
> > > > > --
> > > > > Wayne Snyder, MCDBA, SQL Server MVP
> > > > > Mariner, Charlotte, NC
> > > > > www.mariner-usa.com
> > > > > (Please respond only to the newsgroups.)
> > > > >
> > > > > I support the Professional Association of SQL Server (PASS)
> > > > > and it's community of SQL Server professionals.
> > > > > www.sqlpass.org
> > > > >
> > > > > "Nat Johnson" <NatJohnson@.discussions.microsoft.com> wrote in
> > > > > message
> > > > > news:9A05E7D1-8DA9-44DB-B5E7-5271DEDC3425@.microsoft.com...
> > > > > > Have a report that requires a @.StartDate parameter. This
> > > > > > will equal a ActualDateTime fields in a table.
> > > > > >
> > > > > > I have the following code listed in my where clause
> > > > > >
> > > > > > and (tvo.ActualDateTime = @.StartDate)
> > > > > >
> > > > > > but keeps getting throwing an error when i test. As we are
> > > > > > in New
> > > Zealand
> > > > > > our date format is dd/MM/yyyy but when entering a start
> > > > > > date in this format i
> > > > > > get an "arithmetic overflow error converting expression to
> > > > > > date type smalldatetime". I assume this is because the
> > > > > > database is storing the
> > > field
> > > > > > as
> > > > > > a datetime and its format is MM/dd/yyyy. I have set the
> > > > > > parameter to datatype datetime. I know this is probably
> > > > > > easy to sort, just need a little
> > > > > > assistance.
> > > > > >
> > > > > > Cheers.
> > > > >
> > > > >
> > > > >
> >
> >|||hi, i have this problem after i installed the SP 2 of Reporting Services,
anyone know if SP 2 modify something with the date format?
My reports use type string and not date time but with sp 1 run very well,
after the instalation of sp 2 comes the error "Arithmetic overflow error
converting expression to data type date..." when i put the parameter with the
format ddmmyyyy.
Anyone know where i can find information about this problem?
thank yoou very much!!!
Guillermo
"Chris McGuigan" wrote:
> I know what you mean! I found this out the hard way too!
> If something doesn't seem right in preview, it's often worth deploying
> and seeing if it's OK there. The rendering engine in Preview is not the
> same as in Report Manager.
> Chris
>
> Nat Johnson wrote:
> > Thanks Chris
> >
> > and you were right...works fine once deployed. just testing at
> > preview doesn't show correct date format...oh well at least it
> > works...just wish i hadn't spent so much time trying to fix
> > something i couldn't.
> >
> > have a good day...
> >
> > "Chris McGuigan" wrote:
> >
> > > Nat,
> > > If your parameter is set to datetime, then there is a bug in the
> > > preview tab that doesn't translate to dd/mm/yyyy it assumes US
> > > format. I found the solution to be in the preview tab use
> > > yyyy-mm-dd, it seems to be a universal format for SQL. DateTime is
> > > not 'stored' in any national format, it's just a number which gets
> > > formatted based on locale.
> > >
> > > You'll probably find it works OK when deployed!
> > >
> > > Chris
> > >
> > >
> > > AshVsAOD wrote:
> > >
> > > > Check the code of your report. The second to last line in your
> > > > XML will be: <Language>en-US</Language>
> > > >
> > > > Change it to:
> > > >
> > > > <Language>en-NZ</Language>
> > > >
> > > >
> > > >
> > > > Also ensure you have SP1 at least installed.
> > > >
> > > > "Nat Johnson" <NatJohnson@.discussions.microsoft.com> wrote in
> > > > message news:F5DCEDDB-4819-4C5F-BC8B-44A4CFA80163@.microsoft.com...
> > > > > Cheers Wayne
> > > > >
> > > > > I have run the report in the preview tab without the parameter
> > > > > statement
> > > > in
> > > > > the where clause and i get data returned.
> > > > >
> > > > > The datatype of the datetime field that I need the @.StartDate
> > > > > parameter to match is of smalldatetime type.
> > > > >
> > > > > With the @.StartDate parameter set to datetime I get data
> > > > > returned, no problem there. But only if i enter the date into
> > > > > the parameter box as MM/dd/yyyy. I want to be able to enter it
> > > > > as dd/MM/yyyy and have it
> > > > display
> > > > > the correct data.
> > > > >
> > > > > hope this makes it a bit clearer.
> > > > >
> > > > > i assume i have to convert the date time just not sure on the
> > > > > syntax
> > > > required
> > > > >
> > > > >
> > > > >
> > > > > "Wayne Snyder" wrote:
> > > > >
> > > > > > Are you getting this error AFTER you set the parameter to
> > > > > > datetime?
> > > > > >
> > > > > > It is understandable when the parameter is string, you'd have
> > > > > > to format
> > > > it
> > > > > > correctly before sending it to SQL..
> > > > > >
> > > > > > --
> > > > > > Wayne Snyder, MCDBA, SQL Server MVP
> > > > > > Mariner, Charlotte, NC
> > > > > > www.mariner-usa.com
> > > > > > (Please respond only to the newsgroups.)
> > > > > >
> > > > > > I support the Professional Association of SQL Server (PASS)
> > > > > > and it's community of SQL Server professionals.
> > > > > > www.sqlpass.org
> > > > > >
> > > > > > "Nat Johnson" <NatJohnson@.discussions.microsoft.com> wrote in
> > > > > > message
> > > > > > news:9A05E7D1-8DA9-44DB-B5E7-5271DEDC3425@.microsoft.com...
> > > > > > > Have a report that requires a @.StartDate parameter. This
> > > > > > > will equal a ActualDateTime fields in a table.
> > > > > > >
> > > > > > > I have the following code listed in my where clause
> > > > > > >
> > > > > > > and (tvo.ActualDateTime = @.StartDate)
> > > > > > >
> > > > > > > but keeps getting throwing an error when i test. As we are
> > > > > > > in New
> > > > Zealand
> > > > > > > our date format is dd/MM/yyyy but when entering a start
> > > > > > > date in this format i
> > > > > > > get an "arithmetic overflow error converting expression to
> > > > > > > date type smalldatetime". I assume this is because the
> > > > > > > database is storing the
> > > > field
> > > > > > > as
> > > > > > > a datetime and its format is MM/dd/yyyy. I have set the
> > > > > > > parameter to datatype datetime. I know this is probably
> > > > > > > easy to sort, just need a little
> > > > > > > assistance.
> > > > > > >
> > > > > > > Cheers.
> > > > > >
> > > > > >
> > > > > >
> > >
> > >
>

Date only fields in SQL Server

Does anyone know if Microsoft is planning to add a DATE only data type
to SQLServer.

I know that you could use a datetime and convert/cast or use datepart
to compare, but this can be tedious and error prone.

What is the recommended way to compare date-only fields?

eg if convert(char(11), @.date_field) = convert(getdate(), @.date_field)
-- do something??"Mystery Man" <PromisedOyster@.hotmail.com> wrote in message
news:87c81238.0402100504.7966b095@.posting.google.c om...
> Does anyone know if Microsoft is planning to add a DATE only data type
> to SQLServer.
> I know that you could use a datetime and convert/cast or use datepart
> to compare, but this can be tedious and error prone.
> What is the recommended way to compare date-only fields?
> eg if convert(char(11), @.date_field) = convert(getdate(), @.date_field)
> -- do something??

There's an article in the November 2003 edition of SQL Server Magazine about
TSQL enhancements in Yukon, according to which the answer is yes.

http://www.sqlmag.com/Articles/Inde...ArticleID=40206

As for comparing dates only, you have to use one of the options you noted
above - DATEPART() or CONVERT():

if convert(char(8), col1, 112) = convert(char(8), col2, 112)
begin
...
end

If your application only uses dates, not times, you may be able to assume
that all times are 00:00.000, in which case you can always compare datetime
values directly. But this is a potentially risky assumption, unless you're
sure that all data entry enforces this rule.

Simon|||> What is the recommended way to compare date-only fields?
> eg if convert(char(11), @.date_field) = convert(getdate(), @.date_field)
> -- do something??
I tend to use datediff:

if datediff('day',@.date1,@.date2) = 0 begin ... end|||Mystery Man (PromisedOyster@.hotmail.com) writes:
> What is the recommended way to compare date-only fields?

datecol = @.date

Most of our date columns are of the type aba_date, which is datetime,
with this rule bound to it:

CREATE RULE aba_date AS convert(char(8), @.x, 112) = @.x

And we trust our parameters to be date values.

I should add that we rarely have reason to look at getdate() to get
the current day; we get that from a parameter table, because our
system changes day when it runs its night job, which may not be at
midnight. getdate() is only used for auditing.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

date of the month

I have two fields called orderdate_d, clinetname, how can I retrieve
clientname whose order date is from the 10th of last month to today?
Thanks,select clientname
from yourTable
where orderdate_d between '8/10/2005' and getdate()
Derek Davis
ddavis76@.gmail.com
"qjlee" <qjlee@.discussions.microsoft.com> wrote in message
news:734C2A76-4770-48D2-8611-BA0D0EDBDA90@.microsoft.com...
>I have two fields called orderdate_d, clinetname, how can I retrieve
> clientname whose order date is from the 10th of last month to today?
> Thanks,|||... where orderdate_d >=dateadd(month, datediff(month, 0, getdate())-1, 0)+
9
and orderdate_d < datediff(day, 0, getdate()+1)
qjlee wrote:

>I have two fields called orderdate_d, clinetname, how can I retrieve
>clientname whose order date is from the 10th of last month to today?
>Thanks,
>|||SELECT clinetname FROM table_name
WHERE orderdate_d >= DATEADD(MONTH,-1,
DATEDIFF(DAY,-10,GETDATE())-DAY(GETDATE()))
(I assume you're not going to have order dates of tomorrow and beyond.)
"qjlee" <qjlee@.discussions.microsoft.com> wrote in message
news:734C2A76-4770-48D2-8611-BA0D0EDBDA90@.microsoft.com...
>I have two fields called orderdate_d, clinetname, how can I retrieve
> clientname whose order date is from the 10th of last month to today?
> Thanks,|||> ... dateadd(month, datediff(month, 0, getdate())-1, 0)+9
Ooh, this is clever too, me likey|||:)
Aaron Bertrand [SQL Server MVP] wrote:

>Ooh, this is clever too, me likey
>
>|||Sorry, I should be more clearly say that this query will have to run evey
month on 11th. How I can specify the year part.
Thanks,
"qjlee" wrote:

> I have two fields called orderdate_d, clinetname, how can I retrieve
> clientname whose order date is from the 10th of last month to today?
> Thanks,|||> Sorry, I should be more clearly say that this query will have to run evey
> month on 11th. How I can specify the year part.
You don't need to, that's the beauty of using DATEADD/DATEDIFF, it doesn't
get the year by parsing a stupid string. You can try it by changing your
clock in your dev environment to december of this year and then january of
next year.|||Thank you all.
It works.
"Aaron Bertrand [SQL Server MVP]" wrote:

> You don't need to, that's the beauty of using DATEADD/DATEDIFF, it doesn't
> get the year by parsing a stupid string. You can try it by changing your
> clock in your dev environment to december of this year and then january of
> next year.
>
>|||On Wed, 14 Sep 2005 14:18:34 -0500, Trey Walpole wrote:

> ... dateadd(month, datediff(month, 0, getdate())-1, 0)+9
Hi Trey,
I agree with Aaron :-)
But it can be made even shorter (not in number of characters, but in
number of operations - and, IMO, easier to understand):
DATEADD(month, DATEDIFF(month, '20050201', getdate()), '20050110')
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Date grouping

Hello All,

Noob here. Trying to group by date in report and it is not working as expected. The date fields include a date/times i.e. 12/23/2006 9:45:00 AM. I can change the date properties to make the data appear as 12/23/2006, but when I group on date, it returns multiple rows showing the same date instead of just one row with the one date. The data is aggregated for the grouping so it seems like it should work properly. Here is an example of what I am trying to do.

Current...

Date Data

12/23/2006 9:45:00 AM 12

12/23/2006 10:00:00 AM 8

12/23/2006 10:15:00 AM 5

Want it to be...

Date Data

12/23/2006 25

Using SQL Server 2000 and RS2000

Thanks,

Clint

Try

=DateValue(Fields!Datetime.Value)

in the Edit / Expression for your Datetime group. I think this should get you what you wanted.

Ham

|||

Thank Ham

Would this work if the field is =Fields!DateTimeStamp.Value?

|||Nothing works like trying, yep it worked. thanks so much!|||

xtrout,

I glad that is worked for you. Can you mark this answers so that others can view our solution.

Thanks

Ham

date format problem

i have two fields, one is startDate and other is endDate, i need only date part in start date and date and time in end date, t shows correctly in db but when i write it in xml (using asp.net) the results are as

<startDate>2003-11-16T00:00:00.0000000+05:00</startDate>
<endDate>2003-11-24T00:00:00.0000000+05:00</endDate>That is the default display format for XML. You have a couple of options when dealing with dates in XML:

1. Write the data to XML as a character string (since you only need the date part, you could use SELECT Convert(Varchar(10), MyDate, 101).

2. When you display the XML (or parse it) you can use an XSL transform to format it (I think the XSL function is formatdate). There is a separate XSL function to handle time, IIRC.

Regards,

hmscott

Originally posted by waqas
i have two fields, one is startDate and other is endDate, i need only date part in start date and date and time in end date, t shows correctly in db but when i write it in xml (using asp.net) the results are as

<startDate>2003-11-16T00:00:00.0000000+05:00</startDate>
<endDate>2003-11-24T00:00:00.0000000+05:00</endDate>|||how to write it in sql query form
update t_auction set [status]= 'closed' where endDate < GETDATE()|||I'm sorry, I don't think I understand your question. Are you using the OPENXML method to get data from an XML document? Or is the XML date (2003-11-24T00:00:00.0000000+05:00) a value in a column in a SQL table that you are trying filter by?

Sorry, scratching my head on this one.

hmscott

Originally posted by waqas
how to write it in sql query form
update t_auction set [status]= 'closed' where endDate < GETDATE()|||i have to store the result in an xml file but before that i have to change the stauts from open to close whose date has been expired. in either cases i.e changing status and writing to xml file has this date probleem.|||can you post some more information? tables, scripts, etc.

thnx.

Originally posted by waqas
i have to store the result in an xml file but before that i have to change the stauts from open to close whose date has been expired. in either cases i.e changing status and writing to xml file has this date probleem.

Date format problem

I am setting my date fields to have a custom format of ddMONyy eg 10Jun05 which works fine in the UK & the US but in Japan the format is overridden and the dates come out like this: ddmyy eg 10605. Any ideas why this happens and how I can stop it?
Many thanksIncase anyone has the same problem this is the solution...

(you only need to do this once in your VB.net project)

If System.Globalization.CultureInfo.CurrentCulture.LCID.ToString = 1041 Then
CrystalDecisions.Shared.SharedUtils.RequestLcid = 2057
End If

1041 is the locale id for Japanese
2057 is the locale id for English (UK)
All IDs are here: http://www.w3schools.com/vbscript/func_setlocale.asp

Thanks for your help ;-)

Date format of 0000-00-00 or 00:00:00

I am converting a MySQL database to SQL server 2000. I noticed that in one of the database fields in a table, they set a default value for the field as 0000-00-00. Is this okay for a default value for SQL Server 2005 ? How about a default value of 00:00:00 ?

Will

'00:00:00' is ok, while '0000-00-00' is invalid. A quick test in SQL Server:

create table testDate (id int,t smalldatetime default '00:00:00')

insert into testDate(id) select 1

create table testDate1 (id int,t smalldatetime default '0000-00-00')

insert into testDate1(id) select 1

The 2nd insert will fail because '0000-00-00' is a date out of range. In SQL, there are some instructions when using datatime and smalldatetime data type, please refer to:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_da-db_9xut.asp

Date fields causing screen refresh.

We have many reports where we have date parameter for a begin and end date.
When users either type a date or select it from the calender, it is causing
the screen to refresh when they are done with that field. This is very
anoyning to the users and is turning them off to RSS. Is there a setting
that is causing this?On May 11, 7:09 pm, Tom <T...@.discussions.microsoft.com> wrote:
> We have many reports where we have date parameter for a begin and end date.
> When users either type a date or select it from the calender, it is causing
> the screen to refresh when they are done with that field. This is very
> anoyning to the users and is turning them off to RSS. Is there a setting
> that is causing this?
Most likely, this is caused by one of two things:
- Either, the report parameters have default values set instead of
null (which would require the View Report button to be selected, if
null was the default). You can check this via: Layout view -> Report
tab -> Report Parameters...
- or, the Autorefresh is set (you can check this via: Layout view ->
Report tab -> Report Properties... -> General tab -> Autorefresh).
Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant

Date Fields - To Null or Not to Null

I am new to MS-SQL and I am doing some testing with converting a few of our current tables from Pervasive 2000i format. The first thing I run into is problems with data fields. We have a lot of date fields in our various tables that have no date (00/00/0000) which is correct. It could mean for instance that something has not happened yet (i.e. Posting Date).

Ok here is my question: Is it better not to allow Nulls in Date fields? If so, what is the best practice to have for the default (i.e. 01/01/1901) date?

We use Crystal Reports and a RAD tool to produce our many complex reports. So this will be a very important decision.

Thanks,

FrankMy vote is to allow nulls. I never liked the idea of putting in a bogus date (9/9/9999) to represent a missing date value. I've always thought it was kind of sloppy.

blindman|||I agree... Go NULL all the way

Originally posted by blindman
My vote is to allow nulls. I never liked the idea of putting in a bogus date (9/9/9999) to represent a missing date value. I've always thought it was kind of sloppy.

blindman|||You have 3 options as I see it..

1) Allow Nulls
Pros: Quick
Cons: Requires 3 valued logic, not very indexable

2) Define and Document a "bogus" date as blindman called it
Pros: You actually have a value that can use an index
Cons: Documentation and logic

3) Project away the date field into a new table using a one-to-none or one-to-one constraint.
Pros: Nice and relational...
Cons: Requires an extra join.

Depending on what option you do go for, don't forget that you can setup views to help out ie: ViewValidDates (Good Dates) and ViewNonValidDates (NULLs, BogusDate etc...)|||definitely use nulls

byrmol, since when can you not index a column that has nulls?

or are you implying that the index is never used? i know i've created indexes on columns with nulls (i was on sql 7) but i never bothered to dig into the execution plan to see if the index was actually being used

the "default" date will get you into WAY more trouble than nulls

that is because the "default" date forces upon you a different "third-value logic" and therefore since you have to code for three values anyway, it is better to use nulls, since there are cases (certain sql constructions) where nulls are easier than having always to remember to add "where datefield <> 'xxxx-xx-xx'" to each and every query, which, i guarantee you, somebody (usually yourself) will forget to do

as for the third option, normalizing the date out into a separate table, just so there's no column in the main table with a null in it -- i know where you're coming from but i really don't think you get much from this, because although you can bring in the date with a join, imagine doing that for all the fields in a typical application which can go null, and suddenly you have to code twenty-seven-table-join monstrosities just to get an "original" row

rudy|||null sounds good to me....

Date Fields

Hi all, i would be really grateful if you could help with this one
I have an MS SQL table with amongst others 3 fields -
DatePosted
Dayspostedfor
ExpiryDate
The value inserted into DatePosted id GetDate()
The vaue inserted into Dayspostedfor is eith - 7/14/21 or 28
I'd like ExpiryDate to calculate the other two to create an expiry date -
for instance -
DatePosted 01/01/06
Dayspostedfor 7
ExpiryDate 08/01/06
Any ideas would be really gratefully recieved
thanksGTN170777 wrote:
> Hi all, i would be really grateful if you could help with this one
> I have an MS SQL table with amongst others 3 fields -
> DatePosted
> Dayspostedfor
> ExpiryDate
> The value inserted into DatePosted id GetDate()
> The vaue inserted into Dayspostedfor is eith - 7/14/21 or 28
> I'd like ExpiryDate to calculate the other two to create an expiry date -
> for instance -
> DatePosted 01/01/06
> Dayspostedfor 7
> ExpiryDate 08/01/06
> Any ideas would be really gratefully recieved
> thanks
Lookup the DATEADD function in Books Online.
Is ExpiryDate always supposed to be equal to DatePosted +
Dayspostedfor? If so, then one of those columns is redundant. You
should decide which two of the columns to keep and drop the other from
the table. That's because redundant data leads to errors,
inefficiencies and over-complexity. If you just want to show both
values on the screen or in a report that doesn't mean you need to hold
them both in the table. You can do the calculation easily whenever you
need to.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||First of all, adopt a single naming convention, namely ISO-11179. Next,
drop the redundant column (columns are not fields!! Important: Columns
can have constraints; fields do not) Start using CURRENT_TIMESTAMP
instead of the proprietary getdate(). Learn to use ISO-8601 date
formats.
CREATE TABLE Postings
(post_date DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
expiry_date DATETIME NOT NULL,
CHECK (post_date < expiry_date),
.);
date <<
Unh? If you already know the expiry date, why compute it? Also, we do
not do computations in an RDBMS. SQL is declarative, so you do such
things outside of the schema and store the data.
--CELKO--
Please post DDL in a human-readable format and not a machine-generated
one. This way people do not have to guess what the keys, constraints,
DRI, datatypes, etc. in your schema are. Sample data is also a good
idea, along with clear specifications.
*** Sent via Developersdex http://www.examnotes.net ***|||ISO-11179 states you should use singular names and not plural.
You should call the table Posting and not Postings.
If you are going to follow a standard then don't invent things to support
your own style.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <remove.jcelko212@.earthlink.net> wrote in message
news:eHU9ewgGGHA.2064@.TK2MSFTNGP09.phx.gbl...
> First of all, adopt a single naming convention, namely ISO-11179. Next,
> drop the redundant column (columns are not fields!! Important: Columns
> can have constraints; fields do not) Start using CURRENT_TIMESTAMP
> instead of the proprietary getdate(). Learn to use ISO-8601 date
> formats.
> CREATE TABLE Postings
> (post_date DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
> expiry_date DATETIME NOT NULL,
> CHECK (post_date < expiry_date),
> ..);
>
> date <<
> Unh? If you already know the expiry date, why compute it? Also, we do
> not do computations in an RDBMS. SQL is declarative, so you do such
> things outside of the schema and store the data.
> --CELKO--
> Please post DDL in a human-readable format and not a machine-generated
> one. This way people do not have to guess what the keys, constraints,
> DRI, datatypes, etc. in your schema are. Sample data is also a good
> idea, along with clear specifications.
>
> *** Sent via Developersdex http://www.examnotes.net ***|||>> ISO-11179 states you should use singular names and not plural. <<
For scalar data elements, not for data structures. I prefer a
collective name over a plural, and a plural name over a singular name
(unless there really is only one row in the table). Thus
Employee = bad. unless you are a very small company :)
Employees = better, since it shows that this is a set of more than one
element
Personnel = best, since it names the collection as such and not as the
grouping of many indivduals|||Hi again, sorry about this, but i am really struggleing with this. I would
like to show the expiry date but as i'm new to MS SQL - I'm lost. I guess th
e
other option would be to have two tables - adDatePosted value (default) =
(convert(char(8),getdate(),112)) and adExpiryDate value = either 7,14,21 or
28 and the run a query like the following through Dreamweaver MX
WHERE adDatePosted + adExpiryDate >= GETDATE()
Would this work? and if so would i need to use 7 or d,7 (7 days) in the
adExpiryDate value?
Thank you
"--CELKO--" wrote:

> For scalar data elements, not for data structures. I prefer a
> collective name over a plural, and a plural name over a singular name
> (unless there really is only one row in the table). Thus
> Employee = bad. unless you are a very small company :)
> Employees = better, since it shows that this is a set of more than one
> element
> Personnel = best, since it names the collection as such and not as the
> grouping of many indivduals
>|||On Tue, 17 Jan 2006 13:16:06 -0800, GTN170777 wrote:

>Hi again, sorry about this, but i am really struggleing with this. I would
>like to show the expiry date but as i'm new to MS SQL - I'm lost. I guess t
he
>other option would be to have two tables - adDatePosted value (default) =
>(convert(char(8),getdate(),112)) and adExpiryDate value = either 7,14,21 or
>28 and the run a query like the following through Dreamweaver MX
>WHERE adDatePosted + adExpiryDate >= GETDATE()
>Would this work? and if so would i need to use 7 or d,7 (7 days) in the
>adExpiryDate value?
>Thank you
Hi GTN170777,
There are three possibilities.
One: have the columns DatePosted (datetime or smalldatetime) and
DaysPostedFor (int, smallint or tinyint) in the table; use the
expression
DATEADD(day, DaysPostedFor, DatePosted)
whenever you need the expiry date.
Two: have the columns DatePosted and ExpiryDate (both datetime or
smalldatetime) in the table; use the expression
DATEDIFF(day, DatePosted, ExpiryDate)
whenever you need the number of days it's posted for.
Three: have the columns DaysPostedFor (int, smallint or tinyint) and
ExpiryDate (datetime or smalldatetime) in the table; use the expression
DATEADD(day, -DaysPostedFor, ExpiryDate)
whenever you need the date posted.
In all cases, if you prefer not to use the same expression over and over
again, you can use a VIEW or a computed column in the table.
Example of VIEW:
CREATE VIEW MyView
AS
SELECT Some other columns,
DatePosted,
DaysPostedFor,
DATEADD(day, DaysPostedFor, DatePosted) AS ExpiryDate
FROM YourTable
Example of computed column:
CREATE TABLE YourTable
(Other columns,
DatePosted smalldatetime NOT NULL,
DaysPostedFor int NOT NULL,
ExpiryDate AS DATEADD(day, DaysPostedFor, DatePosted),
More columns or constraints
)
Hugo Kornelis, SQL Server MVP

date field problem - "Value could not be converted because of a potential loss of data"

Hi,

I have a flat file that has a date column where the date fields look like 20070626, for example. No quotes.

The problem is that several of the date values are missing, and instead of the date value the field looks like this , ,

That is, there are several blank spaces where the date should be. The number of blank spaces between the commas doesn't appear to be a set number (and it could even be 8 blank spaces, I don't know, in which case I don't know if checking for the Len will produce the correct results, but that's another issue...)

So, similar to the numeric field blanks problem, I wrote a script to convert the field to null. This is the logic I used:

IfNot Len(Row.TradeDate) = 8 Then

Row.TradeDate_IsNull = True

EndIf

The next step in my data flow after the script is a derived column where I convert TradeDate from 20070625 to 06/25/2007. So the exact error message I am receiving is this:

[OLE DB Destination [547]] Error: There was an error with input column "TradeDate - derived" (645) on input "OLE DB Destination Input" (560). The column status returned was: "The value could not be converted because of a potential loss of data.".

Do I need to add a conditional split after the script and BEFORE the derived column to redirect bad rows so they don't go to the derived column?

What am I doing wrong here?

Thanks

Actually, I realize I don't want a conditional split because I don't want to throw the whole row away.

I just need to fix this "data conversion" problem.

|||Why not use a derived column to work with the date field and use the trim() function to get rid of the spaces, however many there may be? I see no need to invoke a script for this.

ISNULL(TRIM([TradeDate])) || [TradeDate] == "" || LEN([TradeDate]) < 8 ? NULL(DT_DBTIMESTAMP) : (DT_DBTIMESTAMP)(SUBSTRING([TradeDate],5,2) + "/" + SUBSTRING([TradeDate],7,2) + "/" + SUBSTRING([TradeDate],1,4))|||

Hi,

I see your logic. I removed the script part and updated the derived column with your expression.

Now I get these errors:

[Derived Column [111]] Error: The conditional operation failed.

[Derived Column [111]] Error: The "component "Derived Column" (111)" failed because error code 0xC0049063 occurred, and the error row disposition on "output column "TradeDate - derived" (541)" specifies failure on error. An error occurred on the specified object of the specified component.

[Derived Column [111]] Error: The "component "Derived Column" (111)" failed because error code 0xC0049063 occurred, and the error row disposition on "output column "TradeDate - derived" (541)" specifies failure on error. An error occurred on the specified object of the specified component.

In the flat file conn mgr, TradeDate is defined as a DT_STR, 8. I see that you are casting TradeDate to a DT_DBTIMESTAMP. I don't think that has anything to do with this error, but thought I would mention it.

I really don't know what the problem is here!

Thanks

|||I see I forgot a couple of TRIM() calls. Try this:

ISNULL(TRIM([TradeDate])) || TRIM([TradeDate]) == "" || LEN(TRIM([TradeDate])) < 8 ? NULL(DT_DBTIMESTAMP) : (DT_DBTIMESTAMP)(SUBSTRING([TradeDate],5,2) + "/" + SUBSTRING([TradeDate],7,2) + "/" + SUBSTRING([TradeDate],1,4))

The output of the above is a datetime field. If you don't want that, and instead want it as a string, then just get rid of the (DT_DBTIMESTAMP) cast before the substrings.|||

Hi,

Why is it when I remove the (DT_DBTIMESTAMP) from NULL(DT_DBTIMESTAMP) the expression is bad?

I guess I don't understand this part: NULL(DT_DBTIMESTAMP)

Thanks

|||Nulls have to by typed accordingly. So that is outputting a NULL of type, DT_DBTIMESTAMP. If you want it to be a string, you'll need to do: NULL(DT_STR,10,1252)|||

That's what I figured, but of course I didn't know the syntax for casting to a string.

Casting to a DT_DBDATETIME is fine since that's what it ultimately is in the table.

Anyhow, it works now, thanks!

I see how I could use derived columns to solve the "blank numeric" problem in my previous post, but it's probably easier with the script, since I don't have to define a derived column output name, and it's less verbose.

|||

sadie519590 wrote:

I see how I could use derived columns to solve the "blank numeric" problem in my previous post, but it's probably easier with the script, since I don't have to define a derived column output name, and it's less verbose.

Up to you!

Date field and replication

Is it true that using date fields in tables being
replicated is not recommended? If true, why?
Thanks
Emma
Emma,
I've never heard this before, and many of my articles have datetime columns.
I do have a few thoughts though...
In snapshot replication I can't see how logically there can be any issues.
In transactional, if there is a default of GetDate() which is often used for
the DateAdded field, as in common with other detfaults, it will not be
transferred to the subscriber so no issue there. Queued updating subscribers
and datetime primary keys could conceivably cause problems, but this is a
relatively obscure situation as the recommendation is not to use datetime
columns for PKs anyway.
The only real issues I can think of are to do with merge replication - if
you are using column-level conflict resolution and you have a "date-changed"
column, you'll get conflicts that you didn't necessarily anticipate. The
other issue you might find is if you are using filters based on date, and
the locale is different on the subscriber and publisher, unexpected records
may be filtered out, and a recent poster was using customided conflict
resolution to solve a similar issue.
If anyone else can add to this list I'd also be interested.
HTH,
Paul Ibison
|||are you sure you are not thinking of the timestamp column. SQL Server 7.0
had problems replicating this data type.
Timestamp columns cannot be published by Publishers running SQL Server 7.0
or to Subscribers running SQL Server 7.0.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:#f66WgLFEHA.576@.TK2MSFTNGP11.phx.gbl...
> Emma,
> I've never heard this before, and many of my articles have datetime
columns.
> I do have a few thoughts though...
> In snapshot replication I can't see how logically there can be any issues.
> In transactional, if there is a default of GetDate() which is often used
for
> the DateAdded field, as in common with other detfaults, it will not be
> transferred to the subscriber so no issue there. Queued updating
subscribers
> and datetime primary keys could conceivably cause problems, but this is a
> relatively obscure situation as the recommendation is not to use datetime
> columns for PKs anyway.
> The only real issues I can think of are to do with merge replication - if
> you are using column-level conflict resolution and you have a
"date-changed"
> column, you'll get conflicts that you didn't necessarily anticipate. The
> other issue you might find is if you are using filters based on date, and
> the locale is different on the subscriber and publisher, unexpected
records
> may be filtered out, and a recent poster was using customided conflict
> resolution to solve a similar issue.
> If anyone else can add to this list I'd also be interested.
> HTH,
> Paul Ibison
>
|||Thanks all for the response. I am thinking of the date
field. There are some fields in the database set as
VARCHAR and the vendor is refusing to change them to date
claiming that they will not work with replication.
Emma

>--Original Message--
>are you sure you are not thinking of the timestamp
column. SQL Server 7.0
>had problems replicating this data type.
>Timestamp columns cannot be published by Publishers
running SQL Server 7.0
>or to Subscribers running SQL Server 7.0.
>"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in
message
>news:#f66WgLFEHA.576@.TK2MSFTNGP11.phx.gbl...
have datetime
>columns.
there can be any issues.
which is often used
>for
detfaults, it will not be
Queued updating
>subscribers
problems, but this is a
not to use datetime
merge replication - if
have a
>"date-changed"
necessarily anticipate. The
based on date, and
publisher, unexpected
>records
customided conflict
interested.
>
>.
>
|||the vendor's lying or confused.
"Emma" <eeemore@.hotmail.com> wrote in message
news:1592901c41659$c8659f20$a501280a@.phx.gbl...
> Thanks all for the response. I am thinking of the date
> field. There are some fields in the database set as
> VARCHAR and the vendor is refusing to change them to date
> claiming that they will not work with replication.
> Emma
>
> column. SQL Server 7.0
> running SQL Server 7.0
> message
> have datetime
> there can be any issues.
> which is often used
> detfaults, it will not be
> Queued updating
> problems, but this is a
> not to use datetime
> merge replication - if
> have a
> necessarily anticipate. The
> based on date, and
> publisher, unexpected
> customided conflict
> interested.

Date diff

I have tow date fields and need to display the days between the two dates.
When I do a direct date1 - date2 I get something in the following format
dd:hh:mm:ss
Is there a way to get it show just the days ?
thanksOn Jan 9, 5:43 pm, "shikarishambu" <shikarishamb...@.hotmail.com>
wrote:
> I have tow date fields and need to display the days between the two dates.
> When I do a direct date1 - date2 I get something in the following format
> dd:hh:mm:ss
> Is there a way to get it show just the days ?
> thanks
If I'm understanding you correctly, this expression should help.
=DateDiff("d", Fields!Date1.Value, Fields!Date2.Value)
Regards,
Enrique Martinez
Sr. Software Consultant|||thanks, that worked
"EMartinez" <emartinez.pr1@.gmail.com> wrote in message
news:edd5245d-c6fc-459d-8956-04a63d035e0d@.t1g2000pra.googlegroups.com...
> On Jan 9, 5:43 pm, "shikarishambu" <shikarishamb...@.hotmail.com>
> wrote:
>> I have tow date fields and need to display the days between the two
>> dates.
>> When I do a direct date1 - date2 I get something in the following format
>> dd:hh:mm:ss
>> Is there a way to get it show just the days ?
>> thanks
>
> If I'm understanding you correctly, this expression should help.
> =DateDiff("d", Fields!Date1.Value, Fields!Date2.Value)
> Regards,
> Enrique Martinez
> Sr. Software Consultant|||On Jan 15, 5:08 pm, "shikarishambu" <shikarishamb...@.hotmail.com>
wrote:
> thanks, that worked"EMartinez" <emartinez...@.gmail.com> wrote in message
> news:edd5245d-c6fc-459d-8956-04a63d035e0d@.t1g2000pra.googlegroups.com...
> > On Jan 9, 5:43 pm, "shikarishambu" <shikarishamb...@.hotmail.com>
> > wrote:
> >> I have tow date fields and need to display the days between the two
> >> dates.
> >> When I do a direct date1 - date2 I get something in the following format
> >> dd:hh:mm:ss
> >> Is there a way to get it show just the days ?
> >> thanks
> > If I'm understanding you correctly, this expression should help.
> > =DateDiff("d", Fields!Date1.Value, Fields!Date2.Value)
> > Regards,
> > Enrique Martinez
> > Sr. Software Consultant
You're welcome. Let me know if I can be of further assistance.
Regards,
Enrique Martinez
Sr. Software Consultant

date conversion question

i have 2 fields that i want to concatenate and then convert it's date format. one field is a smalldatetime, the other is a varchar:

u_Departure_Time = varchar(5), ex data is 05:30 or 16:30
EffFrom = smalldatetime, ex data is 09/01/2003

trying to do something like this:
CONVERT(datetime,(dateadd(dd, value, EffFrom)+' '+u_Departure_Time),'mm/dd/yyyy hh:mi:ms AM')

i need to take the varchar 24hr time from u_Departure_Time, concatenate it to EffFrom and make it 12hr format. In the above examples I'd like to obtain:

9/1/2003 5:30:00 AM
9/1/2003 4:30:00 PM

it seems basic, but i'm an oracle guy, just started using sql server.

thanks!select cast(convert(char(10), EffFrom, 101) + ' ' + u_Departure_Time as datetime)|||Try this one:

declare @.date smalldatetime,@.time varchar(10)
set @.date='09/17/2003'
set @.time='16:30'
select cast(@.date+' '+@.time as datetime)|||Originally posted by ms_sql_dba
select cast(convert(char(10), EffFrom, 101) + ' ' + u_Departure_Time as datetime)

cool, i tried this:
,cast(convert(char(10), EffFrom, 101) + ' ' + u_Departure_Time as smalldatetime)

but it's in 24hr time,

sample: 2003-08-01 19:25:00

i need it to match another tables format of dd/mm/yyyy h:mm:ss AM/PM

like: 2003-08-01 7:25:00 PM

this is really a newbie question, but what does the '101' signify?|||101 converts 2003-09-01 to 09/01/2003 format so that you can att time portion to it before converting it back to datetime.

Date Conversion Problem

I am importing a text file into a SQL table, using DTS. My problem is concerning the date fields. The source fields are in the yyyymmdd format. I have tried using datetime transformation, using yyyyMMdd as the source format, and MM/dd/yyyy as the destination formation. If there is a valid date, this works fine. However, many of the dates are either null or contain spaces, and the DTS will not handle them. Any suggestions as to how to handle this?Is the column defined as NOT NULL?

DTS the table a stage table with all of the columns a varchar...

Then manipulate it with sql and do an insert?|||Originally posted by Brett Kaiser
Is the column defined as NOT NULL?

DTS the table a stage table with all of the columns a varchar...

Then manipulate it with sql and do an insert?

-----------

The Allow Nulls option is turned on in the table definition. If I manually add a record in Enterprise Manager, it will accept nulls. It's just the DTS that doesn't like them.

I can use an intermediary table if that's the only way. I was just hoping that it could be done during the initial import.

Thanks for your suggestions.|||Sounds like a fixed width file...

Actually I' m suprised it's not working...

Where's the file coming from?

Mainframe?

Got any unprintable chars there?|||Originally posted by Brett Kaiser
Sounds like a fixed width file...

Actually I' m suprised it's not working...

Where's the file coming from?

Mainframe?

Got any unprintable chars there?

Yes, it is a mainframe file, with fixed width fields. There are no unprintable characters. It's just that some of the date fields are either null or contain spaces (I'm not sure which), and DTS keeps choking on them.|||Can you post the transformation code?

You do know that putting the code in the package like that slows everything down..

You're much better off getting all the data in, then using set based methods to transform the data...

much, much fatser...

Ever use bcp?|||Originally posted by Brett Kaiser
Can you post the transformation code?

You do know that putting the code in the package like that slows everything down..

You're much better off getting all the data in, then using set based methods to transform the data...

much, much fatser...

Ever use bcp?

No, I haven't used bcp before. I'll check it out.

In addition to choosing datetime transformation and setting the formats, I've also tried using an ActiveX script. Here is the ActiveX code I've tried for the transformation:

Function Main()
If Not IsNull(DTSSource("Col010")) AND LEN(TRIM(DTSSource ("Col010"))) > 0 Then (Checking for null or spaces)
DTSDestination("AWARD_DATE") = MID(DTSSource("Col010"),7,2)&"-"&MID(DTSSource("Col010"),5,2)&"-"&LEFT(DTSSource("Col010"),4)
Main = DTSTransformStat_OK
End If
End Function

The error returned is: Invalid procedure call or argument - DTSSource|||Just wondering is your System a AS400 cause i had also ran into this before.|||Originally posted by hillcat
Just wondering is your System a AS400 cause i had also ran into this before.

No, PC with Windows XP Pro & SQL Server 2000|||but is the the mainframe file a rpg file|||Originally posted by hillcat
but is the the mainframe file a rpg file

I'm not familiar with rpg; all I know is, the file is a text file from a mainframe, with fixed width fields. I was given a printout of the file layout to indicate starting and ending point of the fields.|||well Is not null function will not work thats for sure since this is a unprintable caracter and this caracter as a value. if this unprintable caracter is at the begining of a string try to trim the first caracter from the string.|||Originally posted by hillcat
well Is not null function will not work thats for sure since this is a unprintable caracter and this caracter as a value. if this unprintable caracter is at the begining of a string try to trim the first caracter from the string.

I'm not sure whether it is null or spaces; that's why I used both the the 'not isnull' and the 'trim', so that I'd be covered either way. If either is not true (value is null, or value is spaces), then the statements inside the if clause should be bypassed|||My guess here is that if you DTS a column that has space and no transformation, it'll put in null..

But because of the transformation, I guess it thinks there should be a valid value, and then fails.

The other thing is that it might not be space, but other data that doesn't transform to a valid date.

Use a stage table and do some analysis.

Soemthing like

SELECT * FROM myStage99 WHERE ISDATE(yourDateCol) = 0|||Originally posted by Brett Kaiser
My guess here is that if you DTS a column that has space and no transformation, it'll put in null..

But because of the transformation, I guess it thinks there should be a valid value, and then fails.

The other thing is that it might not be space, but other data that doesn't transform to a valid date.

Use a stage table and do some analysis.

Soemthing like

SELECT * FROM myStage99 WHERE ISDATE(yourDateCol) = 0

I'll give it a try. Much thanks . . .|||Like:

USE Northwind
GO

CREATE TABLE myTable99(Col1 varchar(8))
GO

INSERT INTO myTable99(Col1)
SELECT 'yyyymmdd' UNION ALL
SELECT '20040317' UNION ALL
SELECT ' '
GO

-- Show me Valid Dates
SELECT * FROM myTable99 WHERE ISDATE(Col1)=1

-- Show me InValid Dates
SELECT * FROM myTable99 WHERE ISDATE(Col1)=0

--Move to it's Final Destination

CREATE TABLE myTable00(Col1 datetime)
GO

INSERT INTO myTable00(Col1)
SELECT Col1 FROM myTable99 WHERE ISDATE(Col1)=1

SELECT * FROM myTable00
GO

DROP TABLE myTable00
DROP TABLE myTable99
GO

Date Conversion in SQL Server

Hi folks,

Here are the fields I have

eventDate = smallDateTime 2005-12-12 00:00:00
eventTime = varchar(20) 1:00:00
newEventDate = dateTime [desired result: 2005-12-12 1:00:00]

When I run the following script:
update healthEvent
set newEventDate = cast(substring(convert(varchar,eventDate,120),1,10 )+' '+eventTime as DateTime)

I get "Syntax error converting datetime from character string." error.

Any ideas why?

Thanks!

-Parul-- Comment
What in the name of all that is relational and logical would somebody design a POS like this?

-- Answer
Because you are not putting them together properly.

The date string at 1 AM is like this:
2007-05-17 01:00:00.000

The date string at 1 PM is like this:
2007-05-17 13:00:00.000

You are trying to slam the 1 against the space after the date without the leading zero.

Start from the inside and work out. When I convert, I like to explicitly define the size of my char variable inside of the convert instead of letting a varchar guess at what i want (convert(char(10),eventDate,120).

In this case I would use an 11 char convert to pick up the space after the date portion plus a 0 plus the ltrim(rtrim(time)) component.


declare @.eventDate smallDateTime
select @.eventDate = '2005-12-12 00:00:00'
declare @.eventTime varchar(20)
select @.eventTime = '1:00:00'
declare @.newEventDate dateTime --[desired result: 2005-12-12 1:00:00]
set @.newEventDate = cast(substring(convert(varchar,@.eventDate,120),1,1 0 )+' 0'+@.eventTime as DateTime)
select @.newEventDate
-- Results
----------------
2005-12-12 01:00:00.000
(1 row(s) affected)|||Thanks so much for your reply.

Unfortunatley, I am still getting the same error as I try to insert @.newEventDate into another date field.|||Plus do I need that leading zero at all times? What if I have @.eventTime as '11:00:00'|||You will have to do a CASE statement for your time segment on the datalength of the ltrim(rtrim(time segment)) . If len = 5 add the zero else don't.

Date conversion

Hi all,
In my AS400 source I have tables with date fields, if I import a table with a package to a text file, there are dates like “0001-01-01”. Before I import directly my AS400 tables into Access without problems because Access read those date as “1901-0
1-01”. Now we have to move to a SQL server. If I try to import with SQL Package it crash unless I first change the fields in my SQL table to varChar(15), do the importation, change the value “0001-01-01” to “1901-01-01” and then alter the data t
ype back to smalldatetime. I have a lot of tables with numerous date fields. I try to link the AS400 source to my SQL Server, I still could not read tables that have dates like “0001-01-01”. I could not do modification to the AS400 source. Is there a
way to solve that problem?
Thanks.
Jean-Paul
Montreal
JP,
have a look at
select convert (datetime, '2' + substring('0001-01-01',2,20),20)
I have had to remove the first zero and replace it with a 2 for this to
work.
Regards,
Paul Ibison
|||Thanks,
but not all the record have this value. When I import with package, I could go around it. But I need to be live, so, from my SQL Server, I link into the AS/400. If I try to open a AS/400 table that has “0001-01-01” date, it said “conversion failure
.
Regards,
Jean-Paul
|||JP,
how about trying openquery and use as/400 syntax to convert to a valid sql
datetime format? I don't know AS400 syntax for this, but I'm hoping you
might :-)
HTH,
Paul Ibison

Date Conversion

Hi,

Please help me on this conversion. I am using DTS to import data from text file to SQL Server 2000. I have these Date fields in the textfile

Date1 yyyymmdd
Date2 yyyymm

What corresponding data type should I define in SQL server. Datetime and smalldate does not work.

ThanksHowdy

The problem you have ( and I assume your data is text in a text file ) is that datetime expects a certain format for the data.

If you were importing date data in format '2003-09-30 14:00:00.000'
( including the single quotes ) all would work well. I use UK date format. If you are in the US its '2003-30-09 14:00:00.000'

Date format in BOL is not documented well, sadly.

So, may need to alter your text data as part of the DTS package ( not easy, and time consuming), or alternatively , import the data straight into a new table using DTS ( easier ), then modify it to insert the " - " etc to make it the correct format then copy it into another table if needed.

Let me know if I have interpreted your problem correctly.

Cheers,

SG.|||i would not alter the text file, rather, i would DTS it into a table where the datatype of the date fields is char(8) and char(6)

once you have the data loaded, you can then use SELECT INTO syntax to create your "final" table

e.g. if you've loaded yyyymmdd data into fieldx and yyyymm into fieldy, then you'd say

select
cast( left(fieldx,4)
+'-'+substring(fieldx,5,2)
+'-'+substring(fieldx,7,2) as datetime ) as fieldxdate
, cast( left(fieldy,4)
+'-'+substring(fieldy,5,2)
+'-01' as datetime ) as fieldydate
, ...
into newtable
from loadedtable

edit: cut & paste typo
rudy
http://r937.com/|||the temporary table idea works for sure, I have implemented that before. Plus you can use the Date Time String conversion in the Transformation tab. Where the source would be in yyyyMMdd format and the destination would be any of your desired formats.

Hope this helps.