Manipulating Dates using SQL

Question

Post reply

Manipulating Dates using SQL

skipwith

SSC Journeyman

Points: 98
More actions
September 29, 2006 at 9:37 am

#170523

Hi All, A question from a newbie!
I would be grateful for some assistance on the subject below :-
I have a date field 'DD-MM-YYYY' that i need to manipulate into 'YYYYMMDD', please could you show me how this could be done.
Thanks,

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

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

Prasad Bhogadi SSCrazy Eights Points: 9235 More actions · Answer 1

SELECT CONVERT(VARCHAR(8),GETDATE() ,112) replace GETDATE() with your datefield

Prasad Bhogadi
www.inforaise.com

Ram Ram Ten Centuries Points: 1079 More actions · Answer 2

Hi

SELECT REPLACE(CONVERT(VARCHAR(10),GETDATE(),120),'-','')

Ram

John Rowan SSC Guru Points: 56440 More actions · Answer 3

The real question here is why, if your data represents a date, are you not storing the value in a datetime column?

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

Prasad Bhogadi SSCrazy Eights Points: 9235 More actions · Answer 4

I assume that could be for end user presentation, it can be done on the client side also.

Prasad Bhogadi
www.inforaise.com

Gift Peddie SSC Guru Points: 73570 More actions · Answer 5

This could help in SQL Server or .NET. Hope this helps.

http://sqlserver2000.databases.aspfaq.com/can-i-make-sql-server-format-dates-and-times-for-me.html

http://authors.aspalliance.com/aspxtreme/sys/globalization/demos/DateTimeFormatInfoGetAllDateTimePatterns.aspx

Kind regards,
Gift Peddie

John Rowan SSC Guru Points: 56440 More actions · Answer 6

What is the datatype of your column? If your raw data looks like you've posted 'DD-MM-YYYY' , then your column has to be a char/varchar column correct?? If so, none of the solutions that have been provided will work for you.

Prasad's solution implies that your column is already a datetime column. If it is a char/varchar, this coversion will not work.

Ram's solution also implies that your column is already a datetime column. Again, if it is a char/varchar, this conversion will not work.

The datetime conversion function in Gift Peddie's post (the first link) will also fail due to a datetime/varchar conversion error.

DECLARE @varchar varchar(12)

-- formatting used in original post cannot be datetime

SET @varchar = '29-09-2006'

--Prasad's solution

SELECT CONVERT(VARCHAR(8),@varchar ,112) as 'Prasad''s solution'

--Ram's Solution

SELECT REPLACE(CONVERT(VARCHAR(10),@varchar,120),'-','') as 'Ram''s Solution'

--Function from link in Gift Peddie's post

SELECT dbo.FormatDateTime(@varchar,'YYYYMMDD') as 'Gift Peddie''s post'

So the question remains, why are you storing data meant to represent dates in a non datetime column? In order to get the date into the format you desire, you must first convert it to a datetime, then convert back to a varchar with the proper style. You must also convert it to datetime anytime you with to use this date in any sort of comparison.

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

Vladan SSC-Insane Points: 22072 More actions · Answer 7

Maybe skipwith has a table with dates stored in varchar (in the wrong format) and wants to change them into correct format in order to perform conversion into DATETIME and fill a new datetime column?

OK, some possibilities - I used variable to test result easily; you can replace variable with your column name.

If you just want to shuffle the characters in the string:

DECLARE @column varchar(20)

SET @column='DD-MM-YYYY'

SELECT SUBSTRING(@column, 7, 4) + SUBSTRING(@column, 4, 2) + SUBSTRING(@column, 1, 2)

If you want to convert directly into datetime:

DECLARE @column varchar(20)

SET @column= '21-02-2005'

SELECT CONVERT(DATETIME, @column, 105)

What is the purpose of the query? If it is one-time action to convert old values or something performed on intermediary table during import of data from text files, it should be alright. If it is something you want to use permanently (i.e. leave the column in the DB as it is and convert every time you access the data), it is bad... as has already been said, store dates in DATETIME data type.