How to fix my datetime seconds

Question

How to fix my datetime seconds

team.bernard

Old Hand

Points: 300
More actions
January 5, 2018 at 1:06 pm

#325291

Hello,
I have an application with a list of agenda.
One person can assign meetings to someone manually. The assignment is about hour and minutes, but seconds aren't visible.
So the meeting is maybe at 16:00:30 or 16:00:31 and so on. These seconds are precious, because the view isn't correct and maybe confuse the user reading (yes, you read well).
How to make a function or a query to fix hours, with a list of allowed hours as 8,10,12,14,16,18 with 00:00:00.000 at the end ?
The aim is to create trigger and make this automatically each month.
A sample list :
2018-01-20 08:00:30.000
2018-01-20 12:00:57.000
2018-01-19 11:57:32.000
Thanks for your help.
Regards

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

team.bernard - Friday, January 5, 2018 1:06 PM
Hello,
I have an application with a list of agenda.
One person can assign meetings to someone manually. The assignment is about hour and minutes, but seconds aren't visible.
So the meeting is maybe at 16:00:30 or 16:00:31 and so on. These seconds are precious, because the view isn't correct and maybe confuse the user reading (yes, you read well).
How to make a function or a query to fix hours, with a list of allowed hours as 8,10,12,14,16,18 with 00:00:00.000 at the end ?
The aim is to create trigger and make this automatically each month.
A sample list :
2018-01-20 08:00:30.000
2018-01-20 12:00:57.000
2018-01-19 11:57:32.000
Thanks for your help.
Regards

I'm not sure that I'm able to understand. To remove seconds, the easiest option is to convert the values into smalldatetime. If you need to round into predefined values, we would need more rules. Or maybe you just want to create the list of possible dates and times available?

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

team.bernard Old Hand Points: 300 More actions · Answer 2

Hello,

I have the list of hours part allowed :

08:00:00:00.000
10:00:00:00.000
12:00:00:00.000
14:00:00:00.000
16:00:00:00.000
18:00:00:00.000

I want to fix datetime in my database by these hours. Round up or down to match this list.

I can't change any field type, I'm the client of the database and can't change the schema.

Luis Cazares SSC Guru Points: 183706 More actions · Answer 3

team.bernard - Friday, January 5, 2018 1:41 PM
Hello,
I have the list of hours part allowed :
08:00:00:00.000
10:00:00:00.000
12:00:00:00.000
14:00:00:00.000
16:00:00:00.000
18:00:00:00.000
I want to fix datetime in my database by these hours. Round up or down to match this list.
I can't change any field type, I'm the client of the database and can't change the schema.

Should 09:00 round to 08:00 and 09:01 round to 10:00?
Should 23:00 round to 18:00? What about 24:00 or 00:00? What about 01:00?

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

team.bernard Old Hand Points: 300 More actions · Answer 4

Hello,

It's during open hours 8am to 6pm, so it's 8am/10am/12am/2pm/4pm/6pm.

Regards

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 5

team.bernard - Friday, January 5, 2018 2:32 PM
Hello,
It's during open hours 8am to 6pm, so it's 8am/10am/12am/2pm/4pm/6pm.
Regards

We don't know what you mean by having only the even hours listed.

What time do you want to save in your database (or view from your database) if the given time is 09:31:15.997?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

HappyGeek SSCoach Points: 18729 More actions · Answer 6

I agree with everything that has been said, and not sure why you would want a trigger. That said this may be of some assistance although I am fairly certain Jeff or someone will come up with something better:

CREATE TABLE dbo.TestDate (MyId int, MyDate datetime not null)

Go

INSERT INTO dbo.TestDate VALUES

(1, '2018-01-20 08:00:30.000'),

(2, '2018-01-20 12:00:57.000'),

(3, '2018-01-19 11:57:32.000'),

(2, '2019-01-16 14:00:57.000')

SELECT mydate,

CAST(MyDate as date) 'Date',

CAST(mydate as smalldatetime) 'No Mins',

CAST(DATEADD(HOUR, DATEDIFF(HOUR, 0, DATEADD(MINUTE, 30, mydate)), 0) as time) 'Time',

CAST(DATEADD(HOUR, DATEDIFF(HOUR, 0, DATEADD(MINUTE, 30, mydate)), 0) as datetime) 'Date & Time'

FROM testdate

drop table testdate

Please excuse formatting, not sure if it is me or SSC doing this!!!

...