Convert the string '01011900' or '19990101' or any format to date and with required format '01/01/1990'

Question

I have tried with the following sample

SELECT
    FORMAT(CONVERT(DATETIME,'01011900'), 'dd/MM/yyyy') 
FROM
    identities 
WHERE
    id_type = 'VID'

possible duplicate of [SQL convert 'DDMMYY' to datetime](http://stackoverflow.com/questions/13512019/sql-convert-ddmmyy-to-datetime) — ughai, Jul 09 '15 at 05:41
related to http://stackoverflow.com/questions/1509977/convert-varchar-into-datetime-in-sql-server — naveen, Jul 09 '15 at 05:47
you did change the question right now. its not acceptable in SO. — naveen, Jul 09 '15 at 05:51
hi naveen my question was little different so i changed it...SO suggestion popup shown up if i have any change i can edit it.. — vinu vinod, Jul 09 '15 at 06:01
you cannot change the question. its against SO rules. not flagging it as you are a noob. — naveen, Jul 09 '15 at 06:06
"or any format" - okay, suppose someone hands you the date `07/05/10` - quick question - what year is that from? What month? Depending on who you ask that question of, you may get varying responses. If humans can't decide, unambiguously, what that string means as a date, you're going to have a hard time making a computer do the job. And by the way, if you're storing it in a `datetime`, it doesn't **have** a format. — Damien_The_Unbeliever, Jul 09 '15 at 08:19

knkarthick24 · Answer 1 · 2015-07-09T05:33:11.823

0

Your data should be 19000101.So your input needs to be modified first then we need to use Convert to get your appropriate format.

declare @inp varchar(10) = '01011900'

select CONVERT(varchar, cast(right(@inp,4)+''+left(@inp,4) as datetime), 101)

--Output : 01/01/1900

edited Jul 09 '15 at 05:33

answered Jul 09 '15 at 05:18

knkarthick24

3,106
15
21

Thanks karthick ...cant we do 19990102 to 01/02/1990 to the specified format without changing the input – vinu vinod Jul 09 '15 at 06:04
Your data should be either 19000101 or 01011900. First you need to work around to set that. Then you can use SQL Server Convert Functions to get the desired output format – knkarthick24 Jul 09 '15 at 06:07

Jesuraja · Accepted Answer · 2015-07-09T07:08:20.697

0

Try this:

SELECT FORMAT(CONVERT(DATETIME,STUFF(STUFF('01011900',5,0,'/'),3,0,'/')),'dd/MM/yyyy')

Insert / using STUFF, and then convert it.

STUFF(STUFF('01011900',5,0,'/'),3,0,'/') -- 01/01/1900

Update: I tried the following also,

DECLARE @DateString varchar(10) = '12202012' --19991231 --25122000  
DECLARE @DateFormat varchar(10)
DECLARE @Date datetime

BEGIN TRY
    SET @Date = CAST(@DateString AS DATETIME)
    SET @DateFormat = 'Valid'
END TRY
BEGIN CATCH
    BEGIN TRY
        SET @DateFormat = 'ddMMyyyy'
        SET @Date = CONVERT(DATETIME,STUFF(STUFF(@DateString,5,0,'/'),3,0,'/'))
    END TRY
    BEGIN CATCH
        SET @DateFormat = 'MMddyyyy'
        SET @Date = CONVERT(DATETIME,STUFF(STUFF(@DateString,1,2,''),3,0,
                            '/' + LEFT(@DateString,2) + '/'))
    END CATCH
END CATCH

SELECT
    @DateString     InputDate,
    @DateFormat     InputDateFormat,
    @Date           OutputDate

edited Jul 09 '15 at 07:08

answered Jul 09 '15 at 05:21

Jesuraja

3,774
4
24
48

thanks jesuraja..its working...! can we do the formatting as independent like dd/mm/yyyy or yyyy/mm/dd ? – vinu vinod Jul 09 '15 at 05:27
dd/mm/yyyy is format 103. Use that in your `convert()` call. – shawnt00 Jul 09 '15 at 05:28
What is the format of '01011900'? *'ddmmyyyy` or 'mmddyyyy'*? – Jesuraja Jul 09 '15 at 05:38
My answer is wrong if it is in *'ddmmyyyy'* format. – Jesuraja Jul 09 '15 at 05:39
the format will be changing in my requirement sometimes it may be 01011990-MM/dd/yyyy or 19990102-yyyy/MM/dd – vinu vinod Jul 09 '15 at 05:41
bad pratice @Jesuraja: writing business logic in `catch` you must pass the dateformat to your SP and use a select instead. tell me, `20122012` will be which format? inclined to downvote this. – naveen Jul 09 '15 at 14:50
Yes, I know. I didn't say this is correct answer. But I tried this, and I posted it. May be somebody else will have the best solution for this. – Jesuraja Jul 09 '15 at 19:10

score 0 · Answer 3 · edited May 23 '17 at 12:06

Right now your question is absurd. There cannot be a generic format that can handle all the date formats, you need to provide that information to your query ( you would be much better of with a stored procedure in this scenario ) where you pass the date format string too. You could do something like this in your SELECT CASE

if the format is ddmmyyyy, do what JesuRaja suggested. But its already answered in SO

SELECT CONVERT (datetime,  Stuff(Stuff('311012',5,0,'.'),3,0,'.'), 4)

if its mmddyyyy, see this answer

DECLARE @Date char(8)
SET @Date='12312009'
SELECT CONVERT(datetime,RIGHT(@Date,4)+LEFT(@Date,2)+SUBSTRING(@Date,3,2))

if its yyyymmdd, just do a CAST

SELECT CAST('20041223' AS datetime)

Bottomline is, you must specify the date format. For example, consider the string 20122012. Can you guess the date format?

Also, you cannot force the server to use / as your separator. The SQL Server will use either /, . or - depending on the Culture of the server.

Thanks naveen..ur comments was helpful – vinu vinod Jul 09 '15 at 09:01 — vinu vinod, Jul 09 '15 at 09:01

Convert the string '01011900' or '19990101' or any format to date and with required format '01/01/1990'

3 Answers3