DateTime format

Hello all,

I'm trying to write a query against an exisiting table that i can't modify and i'm running into a bit of a problem. The table stores timestamps as a char field instead of a datetime.

So, i've had to use the CONVERT function to change it to a datetime during my query. A sample is below:

SELECT convert(datetime, logged, 120) FROM AP200310

This works, except i want to include the option of querying a single day. Since the data that is returned is in this format:

12/12/2006 6:54:15 PM

The following sql statement doesn't work:
SELECT convert(datetime, logged, 120) FROM AP200310 WHERE logged = '12/12/2006'

Thanks in advance for any help.

Have you tried using cast instead of convert?|||

You need to handle the logged field because it is a datetime field.

You can try this to get your query to work:

SELECT

CONVERT(NVARCHAR(10),logged,120)

FROM

AP200310

WHERE(CONVERT(NVARCHAR(10),logged,101)='12/12/2006')

|||

Thanks for the quick response.

I tried your code and it didn't seem to work.

Help!!

|||

Can you post the results of the query limno posted? and tell us why it does not work. It seems to work for me.

declare @.ttable (col1int identity, col2char(22))insert into @.tvalues ('12/12/2006 6:54:15 PM')insert into @.tvalues ('12/12/2006 6:55:15 PM')insert into @.tvalues ('12/20/2006 6:54:15 PM')insert into @.tvalues ('10/12/2006 6:54:15 PM')selectconvert(nvarchar(10),col2,120), *from @.twhere(CONVERT(NVARCHAR(10),col2,120) ='12/12/2006')

|||

Here is my query.

SELECT CONVERT(NVARCHAR(10), LOGGED, 120) AS LOGGED
FROM AP200612
WHERE (CONVERT(NVARCHAR(10), LOGGED, 101) = '12/12/2006')

My results are nothing is returned.

To help matters, i've included a copy of the schema of the table in question:

CREATETABLE [dbo].[AP200612](

[ACCOUNT] [char]

(9)COLLATE SQL_Latin1_General_CP1_CI_ASNULLCONSTRAINT [gmc_AP200612ACCOUNT]DEFAULT(''),

[LOGGED] [char]

(19)COLLATE SQL_Latin1_General_CP1_CI_ASNULLCONSTRAINT [gmc_AP200612LOGGED]DEFAULT(''),

[ORIGIN] [decimal]

(3, 0)NULLCONSTRAINT [gmc_AP200612ORIGIN]DEFAULT((0)),

[STAT_NUM] [char]

(5)COLLATE SQL_Latin1_General_CP1_CI_ASNULLCONSTRAINT [gmc_AP200612STAT_NUM]DEFAULT(''),

[SUBLOC] [char]

(5)COLLATE SQL_Latin1_General_CP1_CI_ASNULLCONSTRAINT [gmc_AP200612SUBLOC]DEFAULT(''),

[SUFFIX] [char]

(2)COLLATE SQL_Latin1_General_CP1_CI_ASNULLCONSTRAINT [gmc_AP200612SUFFIX]DEFAULT(''),

[TERM] [char]

(5)COLLATE SQL_Latin1_General_CP1_CI_ASNULLCONSTRAINT [gmc_AP200612TERM]DEFAULT(''),

[TIME] [char]

(19)COLLATE SQL_Latin1_General_CP1_CI_ASNULLCONSTRAINT [gmc_AP200612TIME]DEFAULT(''),

[TYPE] [char]

(3)COLLATE SQL_Latin1_General_CP1_CI_ASNULLCONSTRAINT [gmc_AP200612TYPE]DEFAULT('')

)

ON [PRIMARY]

Thanks for your responses and any future responses.

Richard M.

|||

I just replaced your column name and table name and it works for me:

You have different format numbers - 120 and 101, both are same though. Can you also post some sample rows?

declare @.ttable (col1int identity, col2char(19))insert into @.tvalues ('12/12/2006 6:54:15')insert into @.tvalues ('12/12/2006 6:55:15')insert into @.tvalues ('12/20/2006 6:54:15')insert into @.tvalues ('10/12/2006 6:54:15')SELECTCONVERT(NVARCHAR(10), col2, 120)AS col2FROM @.tWHERE (CONVERT(NVARCHAR(10), col2, 101) ='12/12/2006')

|||Hi rmethod, ndinakar's solution should work, I'd like know how you insert rows into the AP200612 table. BTW, if the LOGGED column is used to store some date, why not use DATETIME/SMALLDATETIME data type? Then you can use rich?T-SQLDate and Time functions to filter rows based on the LOGGED column.

DATEDIFF in Report Builder

I'm having a bit of trouble with the DATEDIFF function in my Report Model project and in Report Builder.

I am trying to create a new Expression field that will work out a persons age from the current date and their Date of Birth, here is the formula that I have entered,

DATEDIFF("y", NOW(), DOB)

where DOB is the field that holds the persons date of birth in the database.

When I enter this into the formula box and click OK i get the following error,

"Operation is not valid due to the current state of the object."

The detailed error text is

Program Location:

at Microsoft.ReportingServices.Modeling.Expression.GetResultType()
at Microsoft.ReportingServices.ModelDesigner.ModelDesignerControl.m_ListNoItemExpression_Click(Object sender, EventArgs e)
at Microsoft.ReportingServices.ModelDesigner.ProjectModelView.Microsoft.DataWarehouse.Interfaces.ICommandTarget.InvokeCommand(MenuCommand menuCommand)
at Microsoft.DataWarehouse.VsIntegration.Designer.Host.CommandTargetMenuService.InvokeCommand(CommandID commandID)

This error ocurrs both in a Report Model project and also in report builder if I try to create a new field after the model has been deployed.

This also happens for DATEADD as well, I'm thinking it may be a problem with the first parameter but I can't find any reference on how to use this Function properly. Has anyone got either of these working in report builder, even if you don't could you post here to let me know you have the same problem.

Would be good to know i'm not alone in this.

Thanks|||I still really need help on this one.

If someone could load up Report Builder and try to create a new field using the DATEDIFF function this would really help me out.

If you get it working then i'll be able to take that onboard, if you don't get it working then I know i'm not alone.

Any help is appreciated|||

Report Builder? You mean Report Designer?
Why don't you get the age from the Query instead of getting it as a new field in the Report Desinger? I mean, add a calculated field directly to the Query, somehting like

SELECT [whatever you actually have], DATEDIFF(YEAR, GETDATE(), DOB) AS age
[and the rest of your query]

Or maybe I didn't understand your question

|||"When I enter this into the formula box"

Are you creating a field in the Repor Designer?
I wouldn't use NOW(), but Globals!ExecutionTime|||This issue has now been resolved, it was not occurring in Report Designer, it was occuring in Report Builder.

The DATEDIFF function in Report Builder must use the "long" names for the Interval and these must be capatalized.

E.g
"Day" - Will work
"dd" - Won't work
"day" - Won't work|||I have a similar question.

I can't seem to get the DATEDIFF function to work. I am trying to display the date from seven days prior to now.

My textbox has the value of...

=format(dateadd(Day, -7, Globals!ExecutionTime), "M/d")

and the error I get is...

Argument not specified for parameter 'DateValue' of Public Function Day(DateValue as Date) as Integer.

I just can't get my head around this one, and I'm sure it's simple. ANY help would be appreciated!|||Sorry, I meant to say I can't get the DateAdd function to work.|||

Day is a function, so it's expecting a parameter (a date value). I'd try to get that value from the query, not from a formula in a Text Box.
Maybe it's not the best solution, but I'd create a Dataset called DataSet1wkago with this query string:

SELECT DATEADD(DAY, -7, GETDATE()) AS last_week

And in the textbox I'd write

=First(Fields!last_week, "DataSet1wkago")

Again: this could be not the best solution, but it works.
I hope it helps you. Regards

|||Thanks, it's not the best solution, but it is still a solution!|||If you are trying to use this function in Report Builder then you will have to enclose the Interval in quotes e.g.

DATEADD("Day", -7, Globals!ExecutionTime)|||

I have the same problems with the data interval.

=Datediff("Day",Parameters!fromdate.Value,Now())

That I am using in a field in a Reporting Services report. I can not use the datediff in the query as I am using a parameter formattet at datetime.

Any suggestions on how this work or where to find useful documentation on expressions in Report Designer?

Thomas Black

|||

Hi,

try using "d" instead of "Day":

=Datediff("d", Parameters!fromdate.Value, now())

below are the list of 'code':

Setting	Description
yyyy	Year
q	Quarter
m	Month
y	Day of year
d	Day
w	Weekday
ww	Week of year
h	Hour
n	Minute
s	Second

DATEDIFF in Report Builder

I'm having a bit of trouble with the DATEDIFF function in my Report Model project and in Report Builder.

I am trying to create a new Expression field that will work out a persons age from the current date and their Date of Birth, here is the formula that I have entered,

DATEDIFF("y", NOW(), DOB)

where DOB is the field that holds the persons date of birth in the database.

When I enter this into the formula box and click OK i get the following error,

"Operation is not valid due to the current state of the object."

The detailed error text is

Program Location:

at Microsoft.ReportingServices.Modeling.Expression.GetResultType()
at Microsoft.ReportingServices.ModelDesigner.ModelDesignerControl.m_ListNoItemExpression_Click(Object sender, EventArgs e)
at Microsoft.ReportingServices.ModelDesigner.ProjectModelView.Microsoft.DataWarehouse.Interfaces.ICommandTarget.InvokeCommand(MenuCommand menuCommand)
at Microsoft.DataWarehouse.VsIntegration.Designer.Host.CommandTargetMenuService.InvokeCommand(CommandID commandID)

This error ocurrs both in a Report Model project and also in report builder if I try to create a new field after the model has been deployed.

This also happens for DATEADD as well, I'm thinking it may be a problem with the first parameter but I can't find any reference on how to use this Function properly. Has anyone got either of these working in report builder, even if you don't could you post here to let me know you have the same problem.

Would be good to know i'm not alone in this.

Thanks|||I still really need help on this one.

If someone could load up Report Builder and try to create a new field using the DATEDIFF function this would really help me out.

If you get it working then i'll be able to take that onboard, if you don't get it working then I know i'm not alone.

Any help is appreciated|||

Report Builder? You mean Report Designer?
Why don't you get the age from the Query instead of getting it as a new field in the Report Desinger? I mean, add a calculated field directly to the Query, somehting like

SELECT [whatever you actually have], DATEDIFF(YEAR, GETDATE(), DOB) AS age
[and the rest of your query]

Or maybe I didn't understand your question

|||"When I enter this into the formula box"

Are you creating a field in the Repor Designer?
I wouldn't use NOW(), but Globals!ExecutionTime|||This issue has now been resolved, it was not occurring in Report Designer, it was occuring in Report Builder.

The DATEDIFF function in Report Builder must use the "long" names for the Interval and these must be capatalized.

E.g
"Day" - Will work
"dd" - Won't work
"day" - Won't work|||I have a similar question.

I can't seem to get the DATEDIFF function to work. I am trying to display the date from seven days prior to now.

My textbox has the value of...

=format(dateadd(Day, -7, Globals!ExecutionTime), "M/d")

and the error I get is...

Argument not specified for parameter 'DateValue' of Public Function Day(DateValue as Date) as Integer.

I just can't get my head around this one, and I'm sure it's simple. ANY help would be appreciated!|||Sorry, I meant to say I can't get the DateAdd function to work.|||

Day is a function, so it's expecting a parameter (a date value). I'd try to get that value from the query, not from a formula in a Text Box.
Maybe it's not the best solution, but I'd create a Dataset called DataSet1wkago with this query string:

SELECT DATEADD(DAY, -7, GETDATE()) AS last_week

And in the textbox I'd write

=First(Fields!last_week, "DataSet1wkago")

Again: this could be not the best solution, but it works.
I hope it helps you. Regards

|||Thanks, it's not the best solution, but it is still a solution!|||If you are trying to use this function in Report Builder then you will have to enclose the Interval in quotes e.g.

DATEADD("Day", -7, Globals!ExecutionTime)|||

I have the same problems with the data interval.

=Datediff("Day",Parameters!fromdate.Value,Now())

That I am using in a field in a Reporting Services report. I can not use the datediff in the query as I am using a parameter formattet at datetime.

Any suggestions on how this work or where to find useful documentation on expressions in Report Designer?

Thomas Black

|||

Hi,

try using "d" instead of "Day":

=Datediff("d", Parameters!fromdate.Value, now())

below are the list of 'code':

Setting	Description
yyyy	Year
q	Quarter
m	Month
y	Day of year
d	Day
w	Weekday
ww	Week of year
h	Hour
n	Minute
s	Second

Datediff help request

Hello,
I need a bit of help with a a datediff statement. I would like to
replace the year portion of a static month and day in the statement with
the year portion of a getdate().
My code looks like this
Datediff("d" [birthday], 11/30/2004) / 365.24
This give the age, which I would like to then use this to report the
grade of the student.
Basically if a student age is between the nov and nov they are grouped
together in the same grade.
Thanks in advance
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!Assuming birthday is a datetime, this may work for you...
select dateadd(yy,datediff(yy,@.birthday,getdate
()),@.birthday)
"1idesigned" <code@.1idesigned.com> wrote in message
news:OtgJJ1UBEHA.3776@.tk2msftngp13.phx.gbl...
> Hello,
> I need a bit of help with a a datediff statement. I would like to
> replace the year portion of a static month and day in the statement with
> the year portion of a getdate().
> My code looks like this
> Datediff("d" [birthday], 11/30/2004) / 365.24
> This give the age, which I would like to then use this to report the
> grade of the student.
> Basically if a student age is between the nov and nov they are grouped
> together in the same grade.
> Thanks in advance
>
>
> *** Sent via Developersdex http://www.examnotes.net ***
> Don't just participate in USENET...get rewarded for it!|||Thanks for the reply, someone suggested some that looks like this that I
am using,
DECLARE @.MyDate As varchar(10)
SET @.mydate = '11/30/' + cast(year(getdate())as varchar)
SELECT 'GradeLevel' =
CASE
WHEN DateDiff("d", birthdate, @.mydate) /365.25 <9.999 THEN 'Less than
3th Grade'
WHEN DateDiff("d", birthdate, @.mydate) /365.25 >8.999 and
DateDiff("d", birthdate, @.mydate) /365.25 <9.999 THEN '03th Grader'
This allows me not to change the 11/30/yy date.
Thanks
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!