Converting Date and Time to Date

Question

Converting Date and Time to Date

thomas.miller1

SSC Enthusiast

Points: 176
More actions
January 22, 2019 at 4:17 pm

#381257

I have a column that I need to display as date only. Right now I get 2019-01-01 00:00:00
I cant remember the syntax........ugh
I know there are several ways to do it......
go....

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Jeffrey Williams SSC Guru Points: 90314 More actions · Answer 1

thomas.miller1 - Tuesday, January 22, 2019 4:17 PM
I have a column that I need to display as date only. Right now I get 2019-01-01 00:00:00
I cant remember the syntax........ugh
I know there are several ways to do it......
go....

CAST(datecolumn AS date)

If this is going to something like SSRS - then don't bother with cast or convert and set the format in the text field of the report/cell.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

thomas.miller1 SSC Enthusiast Points: 176 More actions · Answer 2

Jeffrey Williams 3188 - Tuesday, January 22, 2019 4:30 PM
thomas.miller1 - Tuesday, January 22, 2019 4:17 PM
I have a column that I need to display as date only. Right now I get 2019-01-01 00:00:00
I cant remember the syntax........ugh
I know there are several ways to do it......
go....
CAST(datecolumn AS date)
If this is going to something like SSRS - then don't bother with cast or convert and set the format in the text field of the report/cell.

CAST(A.SOLD_DATE AS DATE)

I understand the CAST but this did not remove the 00:00:00

Jeff Moden SSC Guru Points: 1004441 More actions · Answer 3

thomas.miller1 - Tuesday, January 22, 2019 4:43 PM
Jeffrey Williams 3188 - Tuesday, January 22, 2019 4:30 PM
thomas.miller1 - Tuesday, January 22, 2019 4:17 PM
I have a column that I need to display as date only. Right now I get 2019-01-01 00:00:00
I cant remember the syntax........ugh
I know there are several ways to do it......
go....
CAST(datecolumn AS date)
If this is going to something like SSRS - then don't bother with cast or convert and set the format in the text field of the report/cell.
CAST(A.SOLD_DATE AS DATE)
I understand the CAST but this did not remove the 00:00:00

Where are you running the code from?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

Jeff Moden - Tuesday, January 22, 2019 9:22 PM
thomas.miller1 - Tuesday, January 22, 2019 4:43 PM
Jeffrey Williams 3188 - Tuesday, January 22, 2019 4:30 PM
thomas.miller1 - Tuesday, January 22, 2019 4:17 PM
I have a column that I need to display as date only. Right now I get 2019-01-01 00:00:00
I cant remember the syntax........ugh
I know there are several ways to do it......
go....
CAST(datecolumn AS date)
If this is going to something like SSRS - then don't bother with cast or convert and set the format in the text field of the report/cell.
CAST(A.SOLD_DATE AS DATE)
I understand the CAST but this did not remove the 00:00:00
Where are you running the code from?

Sounds to me that it is getting converted back to a datetime value after being converted to a date value.

thomas.miller1 SSC Enthusiast Points: 176 More actions · Answer 5

Lynn Pettis - Tuesday, January 22, 2019 11:07 PM
Jeff Moden - Tuesday, January 22, 2019 9:22 PM
thomas.miller1 - Tuesday, January 22, 2019 4:43 PM
Jeffrey Williams 3188 - Tuesday, January 22, 2019 4:30 PM
thomas.miller1 - Tuesday, January 22, 2019 4:17 PM
I have a column that I need to display as date only. Right now I get 2019-01-01 00:00:00
I cant remember the syntax........ugh
I know there are several ways to do it......
go....
CAST(datecolumn AS date)
If this is going to something like SSRS - then don't bother with cast or convert and set the format in the text field of the report/cell.
CAST(A.SOLD_DATE AS DATE)
I understand the CAST but this did not remove the 00:00:00
Where are you running the code from?
Sounds to me that it is getting converted back to a datetime value after being converted to a date value.

Aginity

Jeff Moden SSC Guru Points: 1004441 More actions · Answer 6

thomas.miller1 - Wednesday, January 23, 2019 7:27 AM
Lynn Pettis - Tuesday, January 22, 2019 11:07 PM
Jeff Moden - Tuesday, January 22, 2019 9:22 PM
thomas.miller1 - Tuesday, January 22, 2019 4:43 PM
Jeffrey Williams 3188 - Tuesday, January 22, 2019 4:30 PM
thomas.miller1 - Tuesday, January 22, 2019 4:17 PM
I have a column that I need to display as date only. Right now I get 2019-01-01 00:00:00
I cant remember the syntax........ugh
I know there are several ways to do it......
go....
CAST(datecolumn AS date)
If this is going to something like SSRS - then don't bother with cast or convert and set the format in the text field of the report/cell.
CAST(A.SOLD_DATE AS DATE)
I understand the CAST but this did not remove the 00:00:00
Where are you running the code from?
Sounds to me that it is getting converted back to a datetime value after being converted to a date value.
Aginity

I've no clue as to what that is but it sounds to me like that app is the thing causing you the pain because SSMS will display the CAST(SomeColumn as DATE) without the time element.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

thomas.miller1 SSC Enthusiast Points: 176 More actions · Answer 7

Jeff Moden - Wednesday, January 23, 2019 7:34 AM
thomas.miller1 - Wednesday, January 23, 2019 7:27 AM
Lynn Pettis - Tuesday, January 22, 2019 11:07 PM
Jeff Moden - Tuesday, January 22, 2019 9:22 PM
thomas.miller1 - Tuesday, January 22, 2019 4:43 PM
Jeffrey Williams 3188 - Tuesday, January 22, 2019 4:30 PM
thomas.miller1 - Tuesday, January 22, 2019 4:17 PM
I have a column that I need to display as date only. Right now I get 2019-01-01 00:00:00
I cant remember the syntax........ugh
I know there are several ways to do it......
go....
CAST(datecolumn AS date)
If this is going to something like SSRS - then don't bother with cast or convert and set the format in the text field of the report/cell.
CAST(A.SOLD_DATE AS DATE)
I understand the CAST but this did not remove the 00:00:00
Where are you running the code from?
Sounds to me that it is getting converted back to a datetime value after being converted to a date value.
Aginity
I've no clue as to what that is but it sounds to me like that app is the thing causing you the pain because SSMS will display the CAST(SomeColumn as DATE) without the time element.

I was afraid of that, they don't use management studio here, this app is how they are connecting to the server. I guess I will google it, thanks.

thomas.miller1 SSC Enthusiast Points: 176 More actions · Answer 8

thomas.miller1 - Wednesday, January 23, 2019 7:48 AM
Jeff Moden - Wednesday, January 23, 2019 7:34 AM
thomas.miller1 - Wednesday, January 23, 2019 7:27 AM
Lynn Pettis - Tuesday, January 22, 2019 11:07 PM
Jeff Moden - Tuesday, January 22, 2019 9:22 PM
thomas.miller1 - Tuesday, January 22, 2019 4:43 PM
Jeffrey Williams 3188 - Tuesday, January 22, 2019 4:30 PM
thomas.miller1 - Tuesday, January 22, 2019 4:17 PM
I have a column that I need to display as date only. Right now I get 2019-01-01 00:00:00
I cant remember the syntax........ugh
I know there are several ways to do it......
go....
CAST(datecolumn AS date)
If this is going to something like SSRS - then don't bother with cast or convert and set the format in the text field of the report/cell.
CAST(A.SOLD_DATE AS DATE)
I understand the CAST but this did not remove the 00:00:00
Where are you running the code from?
Sounds to me that it is getting converted back to a datetime value after being converted to a date value.
Aginity
I've no clue as to what that is but it sounds to me like that app is the thing causing you the pain because SSMS will display the CAST(SomeColumn as DATE) without the time element.
I was afraid of that, they don't use management studio here, this app is how they are connecting to the server. I guess I will google it, thanks.

Netezza....

thomas.miller1 SSC Enthusiast Points: 176 More actions · Answer 9

thomas.miller1 - Wednesday, January 23, 2019 7:49 AM
thomas.miller1 - Wednesday, January 23, 2019 7:48 AM
Jeff Moden - Wednesday, January 23, 2019 7:34 AM
thomas.miller1 - Wednesday, January 23, 2019 7:27 AM
Lynn Pettis - Tuesday, January 22, 2019 11:07 PM
Jeff Moden - Tuesday, January 22, 2019 9:22 PM
thomas.miller1 - Tuesday, January 22, 2019 4:43 PM
Jeffrey Williams 3188 - Tuesday, January 22, 2019 4:30 PM
thomas.miller1 - Tuesday, January 22, 2019 4:17 PM
I have a column that I need to display as date only. Right now I get 2019-01-01 00:00:00
I cant remember the syntax........ugh
I know there are several ways to do it......
go....
CAST(datecolumn AS date)
If this is going to something like SSRS - then don't bother with cast or convert and set the format in the text field of the report/cell.
CAST(A.SOLD_DATE AS DATE)
I understand the CAST but this did not remove the 00:00:00
Where are you running the code from?
Sounds to me that it is getting converted back to a datetime value after being converted to a date value.
Aginity
I've no clue as to what that is but it sounds to me like that app is the thing causing you the pain because SSMS will display the CAST(SomeColumn as DATE) without the time element.
I was afraid of that, they don't use management studio here, this app is how they are connecting to the server. I guess I will google it, thanks.
Netezza....

Found it, lol TO_CHAR(SomeColumn,'YYY-MM-DD')....ugh

Luis Cazares SSC Guru Points: 183706 More actions · Answer 10

thomas.miller1 - Wednesday, January 23, 2019 7:52 AM
Found it, lol TO_CHAR(SomeColumn,'YYY-MM-DD')....ugh

That's an Oracle function.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2