remove time from 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

General SQL Server Forums

New to SQL Server Programming

remove time from date

Author

Topic

jtwork
Yak Posting Veteran

82 Posts

Posted - 2007-10-04 : 07:53:06

How can i remove the tim from a date. date/time 2007/10/01 01:00 to just 2007/10/01

ive tried the conversion([date/time],103) but this doesnt work.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-10-04 : 08:04:50

[code]select dateadd(day, 0, datediff(day, 0, getdate()))[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-10-04 : 08:07:59

quote:
Originally posted by harsh_athalye

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

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

More reliable method
select dateadd(day, datediff(day, 0, getdate()),0)

Madhivanan

Failing to plan is Planning to fail

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2007-10-04 : 08:18:23

This turns your date into a string, it looks like a date, but it is varchar

declare @date datetime
set @date = '2007/10/01 01:00 '

select REPLACE(CONVERT(varchar(10),@date,120),'-','/')

Jim

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-10-04 : 08:18:24

@madhivanan:
more reliable? why?

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-10-04 : 08:22:54

quote:
Originally posted by jimf

This turns your date into a string, it looks like a date, but it is varchar

declare @date datetime
set @date = '2007/10/01 01:00 '

select REPLACE(CONVERT(varchar(10),@date,120),'-','/')

Jim

Dont make dates to varchars

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-10-04 : 08:24:01

quote:
Originally posted by spirit1

@madhivanan:
more reliable? why?

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

No difference if you omit time for a day. But see how it behaves for month and year



select 
	dateadd(day, 0,datediff(day, 0, getdate())),
	dateadd(day, datediff(day, 0, getdate()),0),
	dateadd(month, 0,datediff(month, 0, getdate())),
	dateadd(month, datediff(month, 0, getdate()),0),
	dateadd(year, 0,datediff(year, 0, getdate())),
	dateadd(year, datediff(year, 0, getdate()),0)

Madhivanan

Failing to plan is Planning to fail

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2007-10-04 : 08:44:38

I don't make dates in to strings, except for formatting purposes <admonition to do formatting in the front end>. However, the op wanted time removed from his date, and the only way to do that is to turn it in to a string.

Jim

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-10-04 : 09:12:31

quote:
Originally posted by jimf

I don't make dates in to strings, except for formatting purposes <admonition to do formatting in the front end>. However, the op wanted time removed from his date, and the only way to do that is to turn it in to a string.

Jim

I agree on "format-mydate-for-display-only"

Madhivanan

Failing to plan is Planning to fail

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2007-10-04 : 09:26:28

My last company used int and strings for dates (old AS400) with no standards
123 = January 23, 2000 (my favorite, yymmdd as an integer!)
000123 = January 23, 2000
012300 = January 23, 2000
20000123 = January 23, 2000 (as string and int)
01232000 = January 23, 2000 (as string and int)

You had to look at some sample data from the table to determine what format it was in.

There's nothing like reality to disabuse you of your foolish notions!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-10-04 : 09:31:37

Well. If you store dates like 000123, how do you find the maximum or minimum dates?

Madhivanan

Failing to plan is Planning to fail

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2007-10-04 : 10:28:30

"In the realm of the blind the one-eyed man is king". I wrote some code to convert everything in to a real date in SQL Server when I DTS'd the AS400 files in. The Product Managers were amazed when I could give them a list of in-force policies instead of excuses as to why they couldn't get them.

Jim

X002548
Not Just a Number

15586 Posts

Posted - 2007-10-04 : 12:47:54

Where's Jeff?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

renu
Starting Member

47 Posts

Posted - 2007-10-05 : 03:21:40

instead of using

declare @date datetime
set @date = '2007/10/01 01:00 '

select REPLACE(CONVERT(varchar(10),@date,120),'-','/')

i think we can directly use this method

DECLARE @C datetime
set @c='2007/10/01 01:00'

SELECT CONVERT(char,@c,111)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-10-05 : 04:10:23

quote:
Originally posted by renu

instead of using

declare @date datetime
set @date = '2007/10/01 01:00 '

select REPLACE(CONVERT(varchar(10),@date,120),'-','/')

i think we can directly use this method

DECLARE @C datetime
set @c='2007/10/01 01:00'

SELECT CONVERT(char,@c,111)

It depends on where OP wants to show/use formatted dates

Madhivanan

Failing to plan is Planning to fail

kilpasis
Starting Member

1 Post

Posted - 2007-10-05 : 09:52:39

create FUNCTION [dbo].[GetDateOnly] (@DATE datetime)
RETURNS datetime
AS
BEGIN
declare @IDate int
declare @MyDate datetime
select @IDate = FLOOR( cast(@DATE as numeric( 10, 3) ) )
select @MyDate = Cast( @iDate as DateTime )
RETURN( @MyDate )
END

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-10-05 : 09:57:39

Well. There are many methods to trim time part. But it really matters where OP wants to use/show the formatted date

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources