split date and time from datetime

Question

split date and time from datetime

Zei

Old Hand

Points: 315
More actions
March 10, 2009 at 11:21 am

#131602

hi,
i searched how to split the date from datetime data type and i came up with this code(below), the problem is that i dont know where to put it.
select convert(varchar(10),date,101) as date from tblTime
hope you can help me.

Viewing 14 posts - 1 through 13 (of 13 total)

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

Mike - CI SSCarpal Tunnel Points: 4146 More actions · Answer 1

scorpio_zz19 (3/10/2009)
hi,
i searched how to split the date from datetime data type and i came up with this code(below), the problem is that i dont know where to put it.
select convert(varchar(10),date,101) as date from tblTime
hope you can help me.

Substitute your column in for the word date (date, 101) and your table for the word tblTime. Both places you want to replace are bolded above.

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 2

Hi

You can avoid the VARCHAR conversion with a little trick based on the structure how SQL Server stores DATETIME values. Convert the DATETIME to FLOAT and use FLOOR to cut into peaces. This should be faster than string conversion.

DECLARE @d DATETIME

SET @d = '20090310 23:45:56'

SELECT @d, CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, @d))), CONVERT(DATETIME, CONVERT(FLOAT, @d) - FLOOR(CONVERT(FLOAT, @d)))

Greets

Flo

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 3

scorpio_zz19 (3/10/2009)
hi,
i searched how to split the date from datetime data type and i came up with this code(below), the problem is that i dont know where to put it.
select convert(varchar(10),date,101) as date from tblTime
hope you can help me.

Before I get into the performance testing on some of the methods shown and more, would you tell us why you need to do such a split? Knowing why something needs to be done often adds clarity as to how something should be done. Thanks.

--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)

SwePeso SSC-Dedicated Points: 39752 More actions · Answer 4

DECLARE@Now DATETIME

SET@Now = GETDATE()

SELECT@Now AS [Now],

DATEADD(DAY, DATEDIFF(DAY, 0, @Now), 0) AS dateOnly,

DATEADD(DAY, DATEDIFF(DAY, @Now, 0), @Now) AS timeOnly

N 56°04'39.16"
E 12°55'05.25"

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 5

Hi

@jeff

Very good objection! I also don't know very much business cases which require this split.

@peso

Also a nice way. Thanks, I did not yet use ;).

Greets

Flo

Zei Old Hand Points: 315 More actions · Answer 6

i'm working with a payroll system wherin the time in and time out will be input basis coz that was the requirement, so how can i compute the total time if there is no data type for time only so i was thinking of spliting the time from date time.

anyway thanks for the help...

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 7

Hello

It is not possible to store only time values within DATETIME columns. With SQL Server 2008 there are new data types for DATE and TIME but not since SQL Server 2005.

My tip would be use a DATETIME field and work with a constant date (e.g. 1900-01-01) and just ignore the date part within your application.

Greets

Flo

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 8

scorpio_zz19 (3/11/2009)
i'm working with a payroll system wherin the time in and time out will be input basis coz that was the requirement, so how can i compute the total time if there is no data type for time only so i was thinking of spliting the time from date time.
anyway thanks for the help...

Just subtract the InTime (datatime datatype) from the OutTime(also a datetime datatype) and warp the answer to your needs. Before I can show you how to do that, what do you need it as? Hours rounded to the nearest tenth?

--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)

dmoldovan SSCertifiable Points: 6334 More actions · Answer 9

Try also:

CONVERT(CHAR(8), GETDATE(), 114)

CONVERT(CHAR(8), GETDATE(), 108)

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 10

dmoldovan (3/12/2009)
Try also:
CONVERT(CHAR(8), GETDATE(), 114)
CONVERT(CHAR(8), GETDATE(), 108)

No... not for the application the OP is going to use and almost never for everything else because both methods are slow due to the character conversion. The only time such conversions should be used in when a specific format is required to be written to files and there is no reporting service or GUI involved.

--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)

dmoldovan SSCertifiable Points: 6334 More actions · Answer 11

Jeff Moden (3/12/2009)
dmoldovan (3/12/2009)
Try also:
CONVERT(CHAR(8), GETDATE(), 114)
CONVERT(CHAR(8), GETDATE(), 108)
No... not for the application the OP is going to use and almost never for everything else because both methods are slow due to the character conversion. The only time such conversions should be used in when a specific format is required to be written to files and there is no reporting service or GUI involved.

Sorry for being obviously wrong here (please, scorpio_zz19, use punctuation in your posts, that will make them far easier to read quickly…). If what scorpio_zz19 needs is a difference between 2 moments in time, I think that DATEDIFF is OK, and no date - time splitting is necessary. However, depending on the requirements, things can get more complicated...

Vijay Sinha Mr or Mrs. 500 Points: 591 More actions · Answer 12

select convert(datetime, convert(float, getdate()) - floor(convert(float, getdate())))

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 13

vijays (3/13/2009)
select convert(datetime, convert(float, getdate()) - floor(convert(float, getdate())))

Unfortunately, this is one of the posts where the original post made by the OP isn't even close to what the OP actually needs to have done. You need to read down a bit where you'll find that what (s)he really wants to do is just calculate the difference between dates to get a duration. Read further down from the top on this thread where the OP says so.

SELECT CONVERT(CHAR(8),EndDate-StartDate,108)

.... will display the difference correctly, but I'm sure it would be better if it were converted to decimal hours... like this...

SELECT CONVERT(DECIMAL(28,1), --Does the formatting

(CONVERT(FLOAT,@EndDate-@StartDate)*24.0)) --Calcs decimal hours

AS RoundedDecimalHours

--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)