date And time - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

date And time

Author

Topic

inbs
Aged Yak Warrior

860 Posts

Posted - 2009-01-26 : 07:06:47

hi,

i have field of type DateTime like : 2009-01-20 08:35:10.270
i want to split it to
column1: 2009-01-20
column2: 08:35:10

i want that type of column1 be datetime

thanks

inbs
Aged Yak Warrior

860 Posts

Posted - 2009-01-26 : 07:12:50

another quation how i can to zero the time i mean instead
2009-01-20 08:35:10.270 i want 2009-01-20 00:00:00

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-26 : 07:24:16

[code]DECLARE @Sample TABLE
(
original DATETIME,
dateOnly DATETIME,
timeOnly DATETIME
)

INSERT @Sample
(
original
)
SELECT '2009-01-20 08:35:10.270'

UPDATE @Sample
SET dateOnly = DATEADD(DAY, DATEDIFF(DAY, 0, original), 0),
timeOnly = DATEADD(DAY, DATEDIFF(DAY, original, 0), original)

SELECT *
FROM @Sample[/code]

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-26 : 07:25:45

-- Down to seconds only

DECLARE	@Sample TABLE
	(
		original DATETIME,
		dateOnly DATETIME,
		timeOnly DATETIME
	)

INSERT	@Sample
	(
		original
	)
SELECT	'2009-01-20 08:35:10.270'

UPDATE	@Sample
SET	dateOnly = DATEADD(DAY, DATEDIFF(DAY, 0, original), 0),
	timeOnly = DATEADD(SECOND, DATEDIFF(SECOND, DATEDIFF(DAY, 0, original), original), 0)

SELECT	*
FROM	@Sample

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-26 : 08:32:37

if by chance you're using sql 2008 you've date datatype and time datatype where you can store date and time values alone

http://www.mssqltips.com/tip.asp?tip=1616

Rajesh Jonnalagadda
Starting Member

45 Posts

Posted - 2009-01-28 : 09:15:49

SELECT LEFT((' 2009-01-20 08:35:10.270'), 11) AS COLUMN1,
SUBSTRING((' 2009-01-20 08:35:10.270') ,CHARINDEX(':',(' 2009-01-20 08:35:10.270'))-2, 8) AS COLUMN2,
CAST(LEFT((' 2009-01-20 08:35:10.270'), 11) AS DATETIME)

Rajesh Jonnalagadda
[url="http://www.ggktech.com
"]GGK TECH[/url]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-28 : 09:22:40

quote:
Originally posted by Rajesh Jonnalagadda

SELECT LEFT((' 2009-01-20 08:35:10.270'), 11) AS COLUMN1,
SUBSTRING((' 2009-01-20 08:35:10.270') ,CHARINDEX(':',(' 2009-01-20 08:35:10.270'))-2, 8) AS COLUMN2,
CAST(LEFT((' 2009-01-20 08:35:10.270'), 11) AS DATETIME)

Rajesh Jonnalagadda
[url="http://www.ggktech.com
"]GGK TECH[/url]

much better to use CONVERT() rather than this

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2009-01-29 : 00:19:46

SELECT CONVERT(VARCHAR(10), GETDATE(), 120) AS [YYYY-MM-DD]

SELECT CONVERT(VARCHAR(8), GETDATE(), 108) AS [HH:MM:SS]

For Format Like 2009-01-20 00:00:00
SELECT DATEADD(d,DATEDIFF(d,0,GETDATE()),0)

Jai Krishna

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources