SUBSTRING a DATETIME value in a SELECT statement

Question

Post reply

SUBSTRING a DATETIME value in a SELECT statement

Trystan Hughes

SSCrazy

Points: 2109
More actions
April 19, 2006 at 10:26 am

#152920

Hi all,
I am having issues when trying to truncate a DATETIME column using the SUBSTRING() function in a SELECT statement. I have tried using CAST and CONVERT functions on the DATETIME column but still get error messages. Is there another way of doing this?
SELECT SUBSTRING(CAST(date AS NVARCHAR(11), 1, 10)) FROM table
I am stumped with this
Hope you can help.
Tryst

Viewing 7 posts - 1 through 6 (of 6 total)

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

Pam Brisjar SSChampion Points: 12094 More actions · Answer 1

What information are you trying to get from the DATETIME column?

sushila SSC-Dedicated Points: 35293 More actions · Answer 2

For starters, get your close paren from the end of the expression to the end of the cast function...

SELECT SUBSTRING(CAST(date AS NVARCHAR(11)), 1, 10)

..and then explain (as pam asked) the requirements...

**ASCII stupid question, get a stupid ANSI !!!**

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

Trys this instead...

SELECT CONVERT(VARCHAR(10),date,101)

... because the supbstring method on date castings will likely not five you the mm/dd/yyyy format that I think you're looking for. And here's some other formating style numbers you can sub for the 101...

yyyymmdd = 112

dd/mm/yyyy = 103

--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)

Trystan Hughes SSCrazy Points: 2109 More actions · Answer 4

Hi, and thanks for the reply people.

I will attempt your solutions when I load up query analyer.

To answer Pam's question, I am trying to get the date from the datatime column (truncate the time part).

Thanks

Tryst

Trystan Hughes SSCrazy Points: 2109 More actions · Answer 5

Trystan Hughes

SSCrazy

Points: 2109

April 20, 2006 at 3:16 am

#633194

Thats sorted it.

Thanks Jeff (and all).

Tryst

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

You bet... thank you for the feedback.

--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)