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

Transact-SQL (2000)

datetime as date and time

Author

Topic

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2006-08-16 : 12:10:29

i have a field called mydate which is a datetime field --
how can I query it so that the date is in one and the time in another --

meaning select date as datefield, time as timefield from entries

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-08-16 : 12:17:25

U mean something like :

Print convert(varchar(30), getdate(), 101)
Print convert(varchar(30), getdate(), 114)

Remember, these r coming as Varchar & not Date time type, if u want to convert it use the convert function again.
For More info have a look at convert in BOL

Srinika

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-08-16 : 12:18:53

SQL Server does not has time only data type. You can use convert() to convert datetime value to date only or time only or date + time.

Or alternatively use your front end application to split date & time.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-08-16 : 12:25:20

You can get the date only with the F_START_OF_DAY function on this link:
Start of Time Period Functions:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=64755

You can get the time only with this function:
Time Only Function: F_TIME_FROM_DATETIME
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=65358

CODO ERGO SUM

drewsalem
Constraint Violating Yak Guru

304 Posts

Posted - 2006-08-16 : 12:32:52

As Sriniki says

Select 
convert(varchar(30), myDate, 114) As 'The_Time',
convert(varchar(30), myDate, 101) As 'The_Date'
From myTable

Thanks

Drew

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2006-08-16 : 13:06:48

got it :) thanks for all your help!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources