Getting rid of the time part 
Author Message
 Getting rid of the time part

How do you get rid of the time part in a datetime field
without doing a convert??



Sun, 18 Dec 2005 15:38:06 GMT
 Getting rid of the time part

Quote:

> How do you get rid of the time part in a datetime field
> without doing a convert??

You can't. A datetime column will always store both date and time. Check it
out in Books Online.

Bob Barrows



Sun, 18 Dec 2005 15:44:53 GMT
 Getting rid of the time part
select convert(varchar, getdate(), 112)

--
-Vishal

Quote:

> How do you get rid of the time part in a datetime field
> without doing a convert??



Sun, 18 Dec 2005 15:47:29 GMT
 Getting rid of the time part
Use DATEPART function
SELECT ltrim(rtrim(str(DATEPART(YY, getdate())))) + '-' +
ltrim(rtrim(str(DATEPART(M, getdate())))) + '-' +
ltrim(rtrim(str(DATEPART(D, getdate()))))

Better use the CONVERT function.

If you do not want the time part at all better declare the column as
smalldatetime

Thanks,
Jagan Mohan
Software Engineer


Quote:

> How do you get rid of the time part in a datetime field
> without doing a convert??



Sun, 18 Dec 2005 15:55:53 GMT
 Getting rid of the time part
1. select cast(datepart(yyyy, getdate()) as char(4)) + '/' + cast(datepart(mm, getdate()) as varchar(2)) + '/' + cast(datepart(dd, getdate()) as varchar(2))
2. Client side.
3. Why don't you want to use Convert?


Quote:

> How do you get rid of the time part in a datetime field
> without doing a convert??



Sun, 18 Dec 2005 15:47:30 GMT
 Getting rid of the time part
Quote:

> Use DATEPART function
> SELECT ltrim(rtrim(str(DATEPART(YY, getdate())))) + '-' +
> ltrim(rtrim(str(DATEPART(M, getdate())))) + '-' +
> ltrim(rtrim(str(DATEPART(D, getdate()))))

> Better use the CONVERT function.

> If you do not want the time part at all better declare the column as
> smalldatetime

Why? Check it out in BOL: smalldatetime stores both date and time as well as
datetime.


Sun, 18 Dec 2005 16:11:54 GMT
 Getting rid of the time part
SELECT CAST(CAST(GETDATE() AS INT) AS DATETIME)

--
Jacco Schalkwijk MCDBA, MCSD, MCSE
Database Administrator
Eurostop Ltd.


Quote:

> How do you get rid of the time part in a datetime field
> without doing a convert??



Sun, 18 Dec 2005 16:46:25 GMT
 Getting rid of the time part
Jacco,

Casting to an int will result in "rounding up" if the time is after noon.
The FLOOR() function is a better choice if one is attempting to do numeric
conversion.  e.g.:

cast(floor(cast(getdate() as float)) as datetime)

Nicole


Quote:
> SELECT CAST(CAST(GETDATE() AS INT) AS DATETIME)

> --
> Jacco Schalkwijk MCDBA, MCSD, MCSE
> Database Administrator
> Eurostop Ltd.



> > How do you get rid of the time part in a datetime field
> > without doing a convert??



Sun, 18 Dec 2005 18:25:39 GMT
 Getting rid of the time part
Good catch Nicole.
SELECT CAST(CAST(dateadd(hh,-12, GETDATE()) AS int) AS DATETIME)
should cover this.


Quote:
> Jacco,

> Casting to an int will result in "rounding up" if the time is after noon.
> The FLOOR() function is a better choice if one is attempting to do numeric
> conversion.  e.g.:

> cast(floor(cast(getdate() as float)) as datetime)

> Nicole



> > SELECT CAST(CAST(GETDATE() AS INT) AS DATETIME)

> > --
> > Jacco Schalkwijk MCDBA, MCSD, MCSE
> > Database Administrator
> > Eurostop Ltd.



> > > How do you get rid of the time part in a datetime field
> > > without doing a convert??



Sun, 18 Dec 2005 18:42:53 GMT
 Getting rid of the time part
Ah, I forgot about the rounding up :$

I know the way to do it wiht floor and I though this was simpler, but
alas...


Quote:
> Jacco,

> Casting to an int will result in "rounding up" if the time is after noon.
> The FLOOR() function is a better choice if one is attempting to do numeric
> conversion.  e.g.:

> cast(floor(cast(getdate() as float)) as datetime)

> Nicole



> > SELECT CAST(CAST(GETDATE() AS INT) AS DATETIME)

> > --
> > Jacco Schalkwijk MCDBA, MCSD, MCSE
> > Database Administrator
> > Eurostop Ltd.



> > > How do you get rid of the time part in a datetime field
> > > without doing a convert??



Sun, 18 Dec 2005 19:17:28 GMT
 Getting rid of the time part
Sorry, didn't see the Floor suggestion... my eyes must be going out on me.
It's also a lot better then 'Dateadd', so I guess my intelligence (or at least the little I had) is going out on me too.
Ahh, the pleasures of getting old.


Good catch Nicole.
SELECT CAST(CAST(dateadd(hh,-12, GETDATE()) AS int) AS DATETIME)
should cover this.


Quote:
> Jacco,

> Casting to an int will result in "rounding up" if the time is after noon.
> The FLOOR() function is a better choice if one is attempting to do numeric
> conversion.  e.g.:

> cast(floor(cast(getdate() as float)) as datetime)

> Nicole



> > SELECT CAST(CAST(GETDATE() AS INT) AS DATETIME)

> > --
> > Jacco Schalkwijk MCDBA, MCSD, MCSE
> > Database Administrator
> > Eurostop Ltd.



> > > How do you get rid of the time part in a datetime field
> > > without doing a convert??



Sun, 18 Dec 2005 19:19:39 GMT
 Getting rid of the time part
last one but not the least one ;0)

SELECT CONVERT(DATETIME, DATEDIFF(DAY, 0, GETDATE()))

Best regards

Daniel

Sorry, didn't see the Floor suggestion... my eyes must be going out on me.
It's also a lot better then 'Dateadd', so I guess my intelligence (or at
least the little I had) is going out on me too.
Ahh, the pleasures of getting old.



Good catch Nicole.
SELECT CAST(CAST(dateadd(hh,-12, GETDATE()) AS int) AS DATETIME)
should cover this.



Quote:
> Jacco,

> Casting to an int will result in "rounding up" if the time is after noon.
> The FLOOR() function is a better choice if one is attempting to do numeric
> conversion.  e.g.:

> cast(floor(cast(getdate() as float)) as datetime)

> Nicole



> > SELECT CAST(CAST(GETDATE() AS INT) AS DATETIME)

> > --
> > Jacco Schalkwijk MCDBA, MCSD, MCSE
> > Database Administrator
> > Eurostop Ltd.



> > > How do you get rid of the time part in a datetime field
> > > without doing a convert??



Mon, 19 Dec 2005 13:56:32 GMT
 
 [ 12 post ] 

 Relevant Pages 

1. Getting rid of the IDENTITY property on a column withou getting rid of the column

2. adding the date part of datetime to the time part of datetime

3. Getting unwanted data that I can't get rid of

4. Getting rid of version control stored procs

5. Getting rid of version control stored procs

6. getting rid of the return status of a stored procedure

7. getting rid of duplicates

8. Getting rid of Primary Log file - SQL 2K

9. Getting rid of 'FindFast' Commands

10. getting rid of a connection?

11. Getting rid of IDENTITY

12. getting rid of duplicates


 
Powered by phpBB® Forum Software