How to get date without conversion? 1

[ClaireHCM]

I saw this question in some other forum.How to do this without conversion?

-----------------------------------------------------------

Hi , i just want a select statement can bring me a date, but "DATETIME" !. I need it because later i want to perform other calculation with these dates.

select client_id,anydate from CLIENTS
and i got something like 1 2002-03-19 18:00
How can i simply get a date? (not a varchar).

----------------------------------------------------------

thanks

 

[MAK9974]

You cant. SQL Server stores date in datetime format.

even the following statement returns date with 00:00:000 as time part. You need to use varchar.

select convert(datetime,left(getdate(),12))

 

[swampBoogie]

You can use convert to get a datetime with the time 00:00:00 (still being datetime though.)

declare @d datetime

select @d = convert(datetime,convert(varchar,datetimeColumn,103))
from t
where pk = 4711

-- doing further calculation on @d
set @d = dateadd(day,11,@d)


I really don't understand the problem.

 

[ClaireHCM]

Thank you both.

But can I change any format setting to get the date part without using conversion?

claire

 

[swampBoogie]

No. Why is it a problem using convert?

 

[tlbroadbent]

See faq183-3007 - "Why Does SQL Server Display My Data Like That?" and check out the article at the following link.

http://tlbroadbent.home.attbi.com/sql/ss_datetime.htm

If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[ClaireHCM]

I dont really know why the original question asker doesnt want to format as varchar.
But thanks all!

[purple]claire

 

[DiggerDog]

Hi Claire

IF you want to do date calculations without the time the best thing I found is to set the time part of the date to

00:00.000

so if you'r comparing dates e.g. date1 > date 2
the time will be irrevelant

to do this I used the following code

e.g. TheDateTime = 2002-03-19 18:00.000

You want 2002-03-19 00:00.000 because you can't exclude the time part (unless you convert to varchar)

SO

Convert date to varchar(8) type = 1 leaves the date only but as a varchar i.e. '03\19\02'


converting the varchar to datetime returns a datetime value but this will return the date with a time of 00:00.000 i.e. 2002-03-19 00:00.000

Select strTheDate = Convert(Varchar(8), TheDate, 1)

Select TheDate = convert(datetime,strTheDate)

OR in one line

Select TheDate = convert(datetime,(Convert(Varchar(8), TheDate, 1)))

 

[tlbroadbent]

Most date comparisons and conversions can be done using valid date string so conversion to datetime format isn't essential. However, I often want to have only the date component of the datetime column. I prefer to remove the time (or to set the time value to 0) with the following conversion.

Select Convert(datetime, Convert(int, getdate()))

Datetime is stored as a number. Converting the number to integer effectively sets the the time component to zero. Note that this conversion can be done in one statement because the conversions can be nested. The datetime value is converted to integer and the integer value is converted back to a date value. If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[JamesLean]

Terry, I've been trying your suggestion and seem to have uncovered a quirk. Try this script:

DECLARE @dt datetime
SELECT @dt = '20030410 12:01'
SELECT
  @dt,
  CONVERT(decimal(20,15), @dt),
  CONVERT(int, CONVERT(decimal(20,15), @dt)),
  CONVERT(int, @dt),
  CONVERT(datetime, CONVERT(int, @dt))

Notice that converting the decimal value of the datetime to an int simply cuts off the decimal portion (as expected).

However, converting the actual datetime value to an int actually uses rounding meaning any decimal portion of .5 or over rounds up. Obviously you then get the next day when converting back to datetime.

I'm using SQL 7. I'd be interested to see if the same thing happens on 2000. Any thoughts? --James

 

[tlbroadbent]

James thanks for the info. SQL Server 2000 works the same as you've noted for SQL 7. I stand corrected in the matter. Conversion to Integer is not a good idea if you don't understand how it works and obviously I didn't understand. If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[DWSRAMBO]

Just wanted to thank you for your response to my question this afternoon and was unsure how to get this message to you(New user). It worked great.

Thanks
Again
Rich

 

[SemperFiDownUnda]

In laymans terms what you see in a SELECT statement is a automatic conversion because showing you
37725.41918021 for Apr 16 2003 10:03AM isn't much good for you
You can do things like calculations directly on a DATETIME. You want to subtract 5 days from today?
GETDATE() - 5
Want to add 12 hours to the current time?
GETDATE() + .5

DATETIME is a floating point number. The integer portion is the number of days since January 1 1900. The decimal portion is the time of day. .5 being 12 noon
being that DATETIME accuracy is about .003 of a second then you can say the smallest amount you need to add to a DATETIME is 0.0000000347222199
1 second = ~0.000011575 Actually this is more than a second one second is 00:00:00 - 00:00:00.997, adding this number will pop the DATETIME to 00:00:01

So in short even though you see in your output text the date isn't stored like that and you are free to handle the DATETIME as a number and can do calculations as you see fit.

Functions LIKE DATEADD just simplify your calculations.

 

[clarkin]

As I am also working on just using date parts of datetime columns atm, thought I would share my findings with you lot:

As noted above, converting to an INT isn't enough as it rounds to the nearest integer (so anything past morning became part of the next day) - instead try this:

[tt]
DECLARE @dt datetime
SELECT @dt = '20030410 12:01'
SELECT
@dt,
CONVERT(datetime,FLOOR(CONVERT(float,@dt)))
[/tt]

the FLOOR() function converts to the nearest integer that is less than / equal to supplied number (so basically, rounds down).

I recommend you have a look at this page which has a complete break down of the datetime data type in SQL server which i found very helpful. In particular it explains why you shouldn't use the above in a SELECT that you run often on your working DBs (short reason: indexes won't get used).

http://vyaskn.tripod.com/searching_date_time_values.htm

Enjoy,

Colm