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

General SQL Server Forums

New to SQL Server Programming

date and time

Author

Topic

sumalatha
Starting Member

6 Posts

Posted - 2009-03-06 : 02:31:42

am creating a table which has got time and date cols.
am using datetime datatype but i want to insert only time for time cols and date for date col
how can i do that

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-03-06 : 02:37:47

r u using sql 2005 r 2008?

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-03-06 : 08:40:11

And...what are data types for the Date and Time cols in your table?

sumalatha
Starting Member

6 Posts

Posted - 2009-03-07 : 01:14:00

sql server 2000

quote:
Originally posted by bklr

r u using sql 2005 r 2008?

sumalatha
Starting Member

6 Posts

Posted - 2009-03-07 : 01:14:59

in sql server 2000 the data type for storing date and time is datetime

quote:
Originally posted by vijayisonly

And...what are data types for the Date and Time cols in your table?

bklr
Master Smack Fu Yak Hacker

1693 Posts

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

quote:
Originally posted by sumalatha

sql server 2000

[/quote]

why are u storing the date & time in separate columns
can u explain some what briefly

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-03-07 : 01:32:06

quote:
Originally posted by sumalatha

am creating a table which has got time and date cols.
am using datetime datatype but i want to insert only time for time cols and date for date col
how can i do that

This is not possible in SQL Server 2000 with datetime data type. Store the data in one column using datetime data type. And then from a query and your application, you can break them apart and convert them to the proper format.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-03-07 : 01:41:09

Hi try this,

SELECT CONVERT(VARCHAR(10), GETDATE(), 105) 'Date',
STUFF(RIGHT(CONVERT(VARCHAR(20), GETDATE(), 100),7),6,0,' ') 'Time'

sumalatha
Starting Member

6 Posts

Posted - 2009-03-12 : 04:38:04

Thanks you

quote:
Originally posted by Nageswar9

Hi try this,

SELECT CONVERT(VARCHAR(10), GETDATE(), 105) 'Date',
STUFF(RIGHT(CONVERT(VARCHAR(20), GETDATE(), 100),7),6,0,' ') 'Time'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-12 : 12:57:12

quote:
Originally posted by Nageswar9

Hi try this,

SELECT CONVERT(VARCHAR(10), GETDATE(), 105) 'Date',
STUFF(RIGHT(CONVERT(VARCHAR(20), GETDATE(), 100),7),6,0,' ') 'Time'

converting to varchar will make date manipulations difficult like comparison, sorting etc.better to use this

SELECT DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0) AS DatePart,
DATEDIFF(ss,DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0),GETDATE()) AS Timeinseconds

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources