problem with date - 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)

problem with date

Author

Topic

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2010-01-06 : 01:24:21

hi how can i insert and retrieve only date but not time or format like dd-mm-yyyy hh:mi

can you provide one example on this.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-01-06 : 01:40:54

select dateadd(day, 0, datediff(day, 0, dateTimeValueHere))

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2010-01-06 : 01:44:27

hi iam inserting date while iam retrieving it it shoud be only date like dd-mm-yyyy hh:mi how it should be then

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-01-06 : 01:53:00

i don't think i understand...perhaps you could post some sample data...?

If you're inserting just the date, then don't include time values.

insert yourTable (datefield) values('20100106')

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-01-06 : 01:59:56

quote:
Originally posted by rajasekhar857

hi iam inserting date while iam retrieving it it shoud be only date like dd-mm-yyyy hh:mi how it should be then

Dont worry on how dates are stored in the table
When you show them in the front end application, use format function there

Madhivanan

Failing to plan is Planning to fail

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2010-01-06 : 02:10:34

i used this format like select convert(varchar, getdate(), 105) but hh:mi am not able to get then

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-01-06 : 03:01:50

quote:
Originally posted by rajasekhar857

i used this format like select convert(varchar, getdate(), 105) but hh:mi am not able to get then

Where do you want to show converted dates?

Madhivanan

Failing to plan is Planning to fail

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2010-01-06 : 04:01:46

i got it
select CONVERT(varchar,date_column_name,104)

+ ' '

+ SUBSTRING(CONVERT(varchar, date_column_name,108),1,5) from tablename

Format is like this dd-mm-yyyy hh:mi

if i want only hh:mi format any idea on it

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2010-01-06 : 04:08:38

go through these links for formats of the date
http://www.sql-server-helper.com/tips/date-formats.aspx
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=80563

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-06 : 04:14:50

quote:
Originally posted by rajasekhar857

i got it
select CONVERT(varchar,date_column_name,104)

+ ' '

+ SUBSTRING(CONVERT(varchar, date_column_name,108),1,5) from tablename

Format is like this dd-mm-yyyy hh:mi

if i want only hh:mi format any idea on it

as Madhi said its best to do this at your front end. But if you're so particular in doing this in sql do like

SELECT CONVERT(varchar(5),date_column_name,108) FROM table

or with date

SELECT CONVERT(varchar(16),date_column_name,131) FROM table

kishore_pen
Starting Member

49 Posts

Posted - 2010-01-06 : 04:42:51

see CONVERT function in BOL. Wide range of format available.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-01-06 : 05:09:41

quote:
Originally posted by rajasekhar857

i got it
select CONVERT(varchar,date_column_name,104)

+ ' '

+ SUBSTRING(CONVERT(varchar, date_column_name,108),1,5) from tablename

Format is like this dd-mm-yyyy hh:mi

if i want only hh:mi format any idea on it

Again,

Where do you want to show formatted dates?

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources