Can datetime field be split out :date and time? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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 2000 Forums

SQL Server Development (2000)

Can datetime field be split out :date and time?

Author

Topic

Sun Foster
Aged Yak Warrior

515 Posts

Posted - 2006-05-23 : 16:31:18

Can I use code to split out datetime field into two fields: date and time? For example, 3/26/2006 5:15:22 PM become [3/26/2006] and [5:15:22 PM].

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-05-23 : 16:38:16

Jeff Smith has a blog about this:
http://weblogs.sqlteam.com/jeffs/archive/2004/12/02/2954.aspx

Tara Kizer
aka tduggan

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-05-23 : 16:38:45

It can be done in the front end

It can be done in SQL level (not recommended)
SELECT CONVERT(varchar, GETDATE(), 101)
SELECT CONVERT(varchar, GETDATE(), 108)

Read about CONVERT in BOL

Srinika

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-05-23 : 17:45:43

You should read this link; the F_TIME_FROM_DATETIME function will give you the time only:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=65358

The F_START_OF_DAY function on this link will give you the date only:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=64755

CODO ERGO SUM

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2006-05-23 : 18:51:31

SET NOCOUNT ON

CREATE TABLE #results(
conversion INT,
result VARCHAR(55),
code VARCHAR(255))

DECLARE
@min INT,
@max INT,
@date DATETIME

SELECT
@min = 1,
@max = 131,
@date = GETDATE()

WHILE @min <= @max
BEGIN

IF @min BETWEEN 15 AND 19
OR @min = 26
OR @min BETWEEN 27 AND 99
OR @min BETWEEN 115 AND 119
OR @min BETWEEN 122 AND 125
OR @min BETWEEN 127 AND 129
BEGIN
GOTO NEXT_LOOP
END

INSERT #results(
conversion,
result,
code)

SELECT
@min,
CONVERT(VARCHAR,@date,@min),
'SELECT CONVERT(VARCHAR,GETDATE(),' + CAST(@min AS VARCHAR(5)) + ')'

NEXT_LOOP:

SELECT @min = @min + 1
END

SELECT
@date AS datetime_format,
conversion,
result,
code
FROM #results

DROP TABLE #results

That will give you all the ones you can get from CONVERT. Whooptie Doo.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-05-24 : 10:53:21

Derrick - Nice idea there, but surely no need for a loop? We can use a numbers table as below, or here: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47685

declare @numbers table (i int identity(1, 1), x bit)
insert @numbers select top 131 null from master.dbo.syscolumns a, master.dbo.syscolumns b

select 
    GETDATE() as datetime_format,
    i as conversion, 
    CONVERT(VARCHAR,GETDATE(),i) as result, 
    'SELECT CONVERT(VARCHAR,GETDATE(),' + CAST(i AS VARCHAR(5)) + ')' as code
from @numbers
where not (
    i BETWEEN 15 AND 19 
    OR i = 26 
    OR i BETWEEN 27 AND 99
    OR i BETWEEN 115 AND 119
    OR i BETWEEN 122 AND 125
    OR i BETWEEN 127 AND 129)

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources