datetime to smalldatetime

I need to convert a datetime field to smalldatetime.

This particular field we only care about the time portion (an example would be '1899-12-30 13:15:00.000')

For now I created another field say 'newTime' that is smalldatetime, in which I want to "update" to the smalldatetime version of the data. I know this will truncate the ms, but I don't care about that. Also the min date that can be used with smalldatetime is Jan 1 1900.

Not sure how to go about doing this.

you could use the CONVERT function. check out BOL for CONVERT functions.

sample:

SELECTCONVERT(varchar,getdate(), 101)

|||

So lets say I have 2 fields, "oldTime" and "newTime"

oldTime is a datetime data type

newTime is a smalldatetime data type

I want to run a query like

update myTableset newTime = oldTimewhere ...etc...

I get this error

Msg 298, Level 16, State 1, Line 1

The conversion from datetime data type to smalldatetime data type resulted in a smalldatetime overflow error.

Then I ran:

update myTable
set newTime =
(SELECTCONVERT(varchar, oldTime, 101))

I get the error:

Msg 296, Level 16, State 3, Line 1

The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value.

The statement has been terminated.

I'm sure this is because of the date portion in oldTime is < 1900

Would dateadd(dd, 1, oldTime) be be the best way to go about this?

|||

you are probably better off using a VARCHAR instead of smalldatetime. If you have to use smalldatetime, then you cannot put in values with YEAR < 1900. Or you could use one of the CONVERT functions to put only the time part.

for example:

DECLARE @.tsmalldatetime, @.t2datetimeSET @.t2 ='Jul 19 1800 1:14PM'SET @.t =convert(varchar,@.t2,114)PRINT @.t

datetime HOUR function format

I am using reporting services to make a matrix. The row value is the date portion of DateIn. The value is a count of transactions. The column type is the problem. It is the hour part of the timein value.

I got it from the database like this:

{fn HOUR(dbo.[Transaction].[TimeIn])} AS Hour

This works, but gives 24 hour time (and only the hour part, so it looks like 10, 11, 12, 13, 14, etc.)

I want it to look like 10:00 AM, 11:00 AM, 12:00 PM, 1:00, PM, etc.

I have read several books, checked online books, tried format functions... and I'm going nuts. This should be so simple- how do I format this so a human can read it? Thanks

If you need the database to do the conversion, then you can set the format code of the textbox to "t" and use the following expression.

=CDate(Fields!Hour.Value & ":00")

If you can use the raw date value from the database, then you can just set the format code of the textbox to "t". If you are grouping on only the hour, then you can still just get the raw date value from the database and use =Fields!TimeIn.Value.Hour as the group expression.

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!