Conver varchar date to date only

Question

Post reply

Conver varchar date to date only

adonetok

SSCrazy Eights

Points: 8395
More actions
January 11, 2017 at 9:30 am

#325183

One column store date format like varchar below
20151025
How to convert to date only (removes the time portion of a DateTime value) format like 10/25/2015?

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

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

Thom A SSC Guru Points: 99133 More actions · Answer 1

DECLARE @Date VARCHAR(10);

SET @Date = '20151025';

--Assuming format is always yyyyMMdd

SELECT CAST(LEFT(@Date, 4) + '-' + LEFT(RIGHT(@Date,4),2) + '-' + RIGHT(@Date,2) AS DATE);

You should change the format of the date, for example to dd/MM/yyyy or dd MMMM yyyy in your presentation layer.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Eirikur Eiriksson SSC Guru Points: 182918 More actions · Answer 2

Suggest you read up on the CONVERT function!

😎

Example

DECLARE @DSTR VARCHAR(50) = '20151025'; -- ISO FORMAT

SELECT CONVERT(VARCHAR(30),CONVERT(DATE,@DSTR,112),101) -- US OUTPUT

Thom A SSC Guru Points: 99133 More actions · Answer 3

Eirikur Eiriksson (1/11/2017)
Suggest you read up on the CONVERT function!
😎
Example
DECLARE @DSTR VARCHAR(50) = '20151025'; -- ISO FORMAT
SELECT CONVERT(VARCHAR(30),CONVERT(DATE,@DSTR,112),101) -- US OUTPUT

The only problem I have with the convert function is having to know what the codes are. Personal preference, I know, but when I'm writing I don't want to have to consult a table to find out what number I need when I'm doing a conversion 🙂

I do admit, yours is cleaner.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

adonetok SSCrazy Eights Points: 8395 More actions · Answer 4

Output is

2015-10-25 00:00:00.000

How to remove 00:00:00.000 portion and still keep date type?

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

adonetok (1/11/2017)
Output is
2015-10-25 00:00:00.000
How to remove 00:00:00.000 portion and still keep date type?

Output of what?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Sergiy SSC Guru Points: 110208 More actions · Answer 6

adonetok (1/11/2017)
One column store date format like varchar below
20151025
How to convert to date only (removes the time portion of a DateTime value) format like 10/25/2015?

What is it for?

What data type you need to get?

_____________
Code for TallyGenerator

adonetok SSCrazy Eights Points: 8395 More actions · Answer 7

Sergiy - Wednesday, January 11, 2017 6:59 PM
adonetok (1/11/2017)
One column store date format like varchar below20151025How to convert to date only (removes the time portion of a DateTime value) format like 10/25/2015?
What is it for?What data type you need to get?

This will be a grid column in the asp.net application.
By clicking column header, grid will sort by that column.
I would like this column

data type = date only
data format= MM/dd/yyyy

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 8

adonetok - Thursday, January 12, 2017 5:45 AM
Sergiy - Wednesday, January 11, 2017 6:59 PM
adonetok (1/11/2017)
One column store date format like varchar below20151025How to convert to date only (removes the time portion of a DateTime value) format like 10/25/2015?
What is it for?What data type you need to get?
This will be a grid column in the asp.net application.
By clicking column header, grid will sort by that column.
I would like this column
data type = date only
data format= MM/dd/yyyy

CONVERT style 101: https://msdn.microsoft.com/en-GB/library/ms187928.aspx

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

sgmunson SSC Guru Points: 110640 More actions · Answer 9

adonetok - Thursday, January 12, 2017 5:45 AM
Sergiy - Wednesday, January 11, 2017 6:59 PM
adonetok (1/11/2017)
One column store date format like varchar below20151025How to convert to date only (removes the time portion of a DateTime value) format like 10/25/2015?
What is it for?What data type you need to get?
This will be a grid column in the asp.net application.
By clicking column header, grid will sort by that column.
I would like this column
data type = date only
data format= MM/dd/yyyy

If you want to be able to SORT that column, then it needs to retain its DATE data type, as otherwise, having it be a text value would not always sort dates correctly. You can format the value from a DATE data type column in your query within your .NET application. But if you don't maintain the date data type there, you might not get the sorting you think you should, unless you get lucky with very limited date ranges.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

MMartin1 One Orange Chip Points: 27799 More actions · Answer 10

adonetok - Thursday, January 12, 2017 5:45 AM
Sergiy - Wednesday, January 11, 2017 6:59 PM
adonetok (1/11/2017)
One column store date format like varchar below20151025How to convert to date only (removes the time portion of a DateTime value) format like 10/25/2015?
What is it for?What data type you need to get?
This will be a grid column in the asp.net application.
By clicking column header, grid will sort by that column.
I would like this column
data type = date only
data format= MM/dd/yyyy

Not sure on the whole scope of the application. Just that I would recommend you feed it a date and not a converted to varchar value unless a conversion (implicit\explicit) to date can be done by the sort operation.

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