Date format question - 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)

Date format question

Author

Topic

Antonio
Posting Yak Master

168 Posts

Posted - 2005-09-29 : 10:19:07

Hi guys,

The following line of code returns the date as well as the time

select getdate()

.. but if I want to return the date with the time set to 0s, I am using the following syntax :

convert(datetime, convert(varchar, getdate, 112), 112)

Is there a better way to achieve the same result?! i.e. the date without the time?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-29 : 10:20:34

Try this

Select DateAdd(day,DateDiff(day,0,getdate()),0)

Madhivanan

Failing to plan is Planning to fail

Bob Whatever
Starting Member

7 Posts

Posted - 2005-09-29 : 10:32:22

A much more clunkier way of doing it, but would let you drop the milliseconds from the time...not sure how to drop the time totally.

SELECT CONVERT(SMALLDATETIME,CONVERT(CHAR,DATEPART(yyyy,GETDATE())) + '-'
+ CONVERT(CHAR,DATEPART(mm,GETDATE())) + '-'
+ CONVERT(CHAR,DATEPART(d,GETDATE()))) AS Today

'The government says they are losing the war on drugs, you know what this means? Theres a war on drugs and the people on drugs are winning it.' - Bill Hicks

Antonio
Posting Yak Master

168 Posts

Posted - 2005-09-29 : 10:32:34

Thanks madhivanan.

Your method also works.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-29 : 10:38:29

quote:
Originally posted by Bob Whatever

A much more clunkier way of doing it, but would let you drop the milliseconds from the time...not sure how to drop the time totally.

SELECT CONVERT(SMALLDATETIME,CONVERT(CHAR,DATEPART(yyyy,GETDATE())) + '-'
+ CONVERT(CHAR,DATEPART(mm,GETDATE())) + '-'
+ CONVERT(CHAR,DATEPART(d,GETDATE()))) AS Today

'The government says they are losing the war on drugs, you know what this means? Theres a war on drugs and the people on drugs are winning it.' - Bill Hicks

so much of conversions are not necessary

Madhivanan

Failing to plan is Planning to fail

Bob Whatever
Starting Member

7 Posts

Posted - 2005-09-29 : 11:20:39

Yeah i realise that now, doing a
convert(smalldatetime,DateAdd(day,DateDiff(day,0,getdate()),0)) with your earlier code would yield the same answer with much less code...

Taxi for Bob! :O)

'The government says they are losing the war on drugs, you know what this means? Theres a war on drugs and the people on drugs are winning it.' - Bill Hicks

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-30 : 01:58:25

>>convert(smalldatetime,DateAdd(day,DateDiff(day,0,getdate()),0))

Still you dont need to have convert

Select DateAdd(day,DateDiff(day,0,getdate()),0) is in valid date Format

Madhivanan

Failing to plan is Planning to fail

rfrancisco
Yak Posting Veteran

95 Posts

Posted - 2005-09-30 : 10:43:11

Here's a link that uses different methods used in a function in getting just the date part of an input date:

http://www.sql-server-helper.com/functions/get-date-only.aspx

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-09-30 : 11:24:53

The method useing DateAdd(day,DateDiff(day,0,getdate()),0) is much better than using CONVERT.

I ran tests with a number of different methods where I converted several million dates, and the method with DateAdd and DateDiff ran in about half the time or less of all the others.

You can also use DateAdd and DateDiff to get many other variations of first of time period:

First day of the month:
DateAdd(month,DateDiff(month,0,getdate()),0)

Last day of the month:
dateadd(day,-1,DateAdd(month,DateDiff(month,0,getdate())+1,0))

First day of next month:
DateAdd(month,DateDiff(month,0,getdate())+1,0)

First day of last month:
DateAdd(month,DateDiff(month,0,getdate())-1,0)

First day of year:
DateAdd(year,DateDiff(year,0,getdate()),0)

Beginning of the hour:
DateAdd(hour,DateDiff(hour,0,getdate()),0)

Beginning of the minute:
DateAdd(minute,DateDiff(minute,0,getdate()),0)

Beginning of a half hour period:
DateAdd(minute,(DateDiff(minute,0,getdate())/30)*30,0)

Beginning of a ten minute period:
DateAdd(minute,(DateDiff(minute,0,getdate())/10)*10,0)

CODO ERGO SUM

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-09-30 : 12:05:08

quote:
Originally posted by Michael Valentine Jones

The method useing DateAdd(day,DateDiff(day,0,getdate()),0) is much better than using CONVERT.

I ran tests with a number of different methods where I converted several million dates, and the method with DateAdd and DateDiff ran in about half the time or less of all the others.

You can also use DateAdd and DateDiff to get many other variations of first of time period:

First day of the month:
DateAdd(month,DateDiff(month,0,getdate()),0)

Last day of the month:
dateadd(day,-1,DateAdd(month,DateDiff(month,0,getdate())+1,0))

First day of next month:
DateAdd(month,DateDiff(month,0,getdate())+1,0)

First day of last month:
DateAdd(month,DateDiff(month,0,getdate())-1,0)

First day of year:
DateAdd(year,DateDiff(year,0,getdate()),0)

Beginning of the hour:
DateAdd(hour,DateDiff(hour,0,getdate()),0)

Beginning of the minute:
DateAdd(minute,DateDiff(minute,0,getdate()),0)

Beginning of a half hour period:
DateAdd(minute,(DateDiff(minute,0,getdate())/30)*30,0)

Beginning of a ten minute period:
DateAdd(minute,(DateDiff(minute,0,getdate())/10)*10,0)

CODO ERGO SUM

Good stuff, Michael !

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-01 : 02:09:43

Very nice MVJ

Madhivanan

Failing to plan is Planning to fail

nosepicker
Constraint Violating Yak Guru

366 Posts

Posted - 2005-10-01 : 03:30:39

Here is an alternative for the last day of the month:

DateAdd(month,DateDiff(month,30,getdate()),30)

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-10-01 : 05:31:06

Interesting, it works, but I can't really figure out why it does. I think I need some sleep.

Along the same idea, this seems to work for last day of the year, but I didn't test very many examples:

select DateAdd(year,DateDiff(year,364,getdate()),364)

Edit:

Here are more along the same line.

Last day of month:
select DateAdd(month,DateDiff(month,-1,getdate()),-1)

Last day of last month:
select DateAdd(month,DateDiff(month,-1,getdate())-1,-1)

Last day of year:
select DateAdd(year,DateDiff(year,-1,getdate()),-1)

Last day of next year:
select DateAdd(year,DateDiff(year,-1,getdate())+1,-1)

quote:
Originally posted by nosepicker

Here is an alternative for the last day of the month:

DateAdd(month,DateDiff(month,30,getdate()),30)

CODO ERGO SUM

rfrancisco
Yak Posting Veteran

95 Posts

Posted - 2005-10-04 : 13:51:48

Michael, how about for the first day of the week, do you have anything similar to what you've posted. Here's one that I found:

http://www.sql-server-helper.com/functions/get-first-day-of-week.aspx

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-10-04 : 14:09:47

Here is a topic in the Script Library forum that contains a function for start of week, and also has code to do it in-line in a SQL query.
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47307

The output of the code in the link you posted will vary, depending on the setting of @@DATEFIRST/SET DATEFIRST. Make sure you understand the issues around that before you use that function, and test it to make sure it works the way you expect. Also, the day the week starts with is preselected for you.

The code in the link I posted is independant of the setting of @@DATEFIRST/SET DATEFIRST, and you select which day you want the week to start with.

quote:
Originally posted by rfrancisco

Michael, how about for the first day of the week, do you have anything similar to what you've posted. Here's one that I found:

http://www.sql-server-helper.com/functions/get-first-day-of-week.aspx

CODO ERGO SUM

rfrancisco
Yak Posting Veteran

95 Posts

Posted - 2005-10-04 : 14:39:29

Thanks Michael for the link and for the response.

But if I want the start of the week based on the value of the @@DATEFIRST, the function in the link that I've provided should work, right? I've tested it and it's giving me the desired output for each possible value for the @@DATEFIRST.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-10-04 : 14:56:39

The point is that @@DATEFIRST is a dynamic property, and may or may not be set the way you expect when you run the function. That is why I selected a method that does not depend on the setting.

quote:
Originally posted by rfrancisco

Thanks Michael for the link and for the response.

But if I want the start of the week based on the value of the @@DATEFIRST, the function in the link that I've provided should work, right? I've tested it and it's giving me the desired output for each possible value for the @@DATEFIRST.

CODO ERGO SUM

rfrancisco
Yak Posting Veteran

95 Posts

Posted - 2005-10-04 : 15:01:55

Michael, thanks again for the reply. I am done hijacking this question :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources