is it possible to achieve String to DateTime Conversion in sql server like mysql?

Question

I have created store procedure in mysql for string to datetime conversion. in my application data is dynamic I mean user import CSV file so only user know data format. so user specify data format like DD/MM/YYYY or YYYY-MM-DD or anything the data contain with any separator. I parse this Date Format and convert into mysql string to date conversion format like %d/%m/%Y and use STR_TO_DATE function. Everything works perfect.

Now I want to achieve same functionality in sql server but in sql server there are some numbers for each date format like 100 in below example.

SELECT convert(datetime, 'Oct 23 2012 11:01AM', 100) -- mon dd yyyy hh:mmAM (or PM)

so in sql server it looks like for some date formats some numbers are fixed. In my case I want to make date format dynamic so user can enter any date format with any separator.

so anyone has any idea how to achieve same string to datetime conversion functionality in sql server same as in mysql i already have achieved and explain as above.

Thanks in advance.

Answer 1

The thirt parameter in the convert function is optional. If you omit it, you can use it to convert different date formats. These all give the same result (at least with my culture settings):

select convert(datetime, 'Oct 23 2012 11:01AM')
union all
select convert(datetime, '2012-10-23 11:01')
union all
select convert(datetime, '2012/10/23 11:01')
union all
select convert(datetime, '10/23/2012 11:01')
union all
select convert(datetime, '2012 Oct 23 11:01')

Note that the current culture setting will be used to determine some formats. the format 10/23/2012 is a valid date with my settings, but not 23/10/2012. The opposite may be the case with your settings.

Answer 2

I am sorry to say, but the convert using a numeric representing the date format is the only possibility in SQL Server next to creating an own implementation using a stored procedure.

You could optionally create a table containing the various date formats and join that with this statement to get the correct number out, but that sounds not like a real solution to me.

The only solution seems to be to accept this.

Answer 3

I found a sql server function that might assist you in what you want.

CREATE FUNCTION dbo.fnFormatDate (@Datetime DATETIME, @FormatMask VARCHAR(32))
RETURNS VARCHAR(32)
AS
BEGIN
    DECLARE @StringDate VARCHAR(32)
    SET @StringDate = @FormatMask
    IF (CHARINDEX (‘YYYY’,@StringDate) > 0)
       SET @StringDate = REPLACE(@StringDate, ‘YYYY’,
                         DATENAME(YY, @Datetime))
    IF (CHARINDEX (‘YY’,@StringDate) > 0)
       SET @StringDate = REPLACE(@StringDate, ‘YY’,
                         RIGHT(DATENAME(YY, @Datetime),2))
    IF (CHARINDEX (‘Month’,@StringDate) > 0)
       SET @StringDate = REPLACE(@StringDate, ‘Month’,
                         DATENAME(MM, @Datetime))
    IF (CHARINDEX (‘MON’,@StringDate COLLATE SQL_Latin1_General_CP1_CS_AS)>0)
       SET @StringDate = REPLACE(@StringDate, ‘MON’,
                         LEFT(UPPER(DATENAME(MM, @Datetime)),3))
    IF (CHARINDEX (‘Mon’,@StringDate) > 0)
       SET @StringDate = REPLACE(@StringDate, ‘Mon’,
                                     LEFT(DATENAME(MM, @Datetime),3))
    IF (CHARINDEX (‘MM’,@StringDate) > 0)
       SET @StringDate = REPLACE(@StringDate, ‘MM’,
                  RIGHT(’0′+CONVERT(VARCHAR,DATEPART(MM, @Datetime)),2))
    IF (CHARINDEX (‘M’,@StringDate) > 0)
       SET @StringDate = REPLACE(@StringDate, ‘M’,
                         CONVERT(VARCHAR,DATEPART(MM, @Datetime)))
    IF (CHARINDEX (‘DD’,@StringDate) > 0)
       SET @StringDate = REPLACE(@StringDate, ‘DD’,
                         RIGHT(’0′+DATENAME(DD, @Datetime),2))
    IF (CHARINDEX (‘D’,@StringDate) > 0)
       SET @StringDate = REPLACE(@StringDate, ‘D’,
                                     DATENAME(DD, @Datetime))   
RETURN @StringDate
END
GO

The usage of the function is the following:

SELECT dbo.fnFormatDate (getdate(), ‘MM/DD/YYYY’)           – 01/03/2012
SELECT dbo.fnFormatDate (getdate(), ‘DD/MM/YYYY’)           – 03/01/2012
SELECT dbo.fnFormatDate (getdate(), ‘M/DD/YYYY’)            – 1/03/2012
SELECT dbo.fnFormatDate (getdate(), ‘M/D/YYYY’)             – 1/3/2012
SELECT dbo.fnFormatDate (getdate(), ‘M/D/YY’)               – 1/3/12
SELECT dbo.fnFormatDate (getdate(), ‘MM/DD/YY’)             – 01/03/12
SELECT dbo.fnFormatDate (getdate(), ‘MON DD, YYYY’)         – JAN 03, 2012
SELECT dbo.fnFormatDate (getdate(), ‘Mon DD, YYYY’)         – Jan 03, 2012
SELECT dbo.fnFormatDate (getdate(), ‘Month DD, YYYY’)       – January 03, 2012
SELECT dbo.fnFormatDate (getdate(), ‘YYYY/MM/DD’)           – 2012/01/03
SELECT dbo.fnFormatDate (getdate(), ‘YYYYMMDD’)             – 20120103
SELECT dbo.fnFormatDate (getdate(), ‘YYYY-MM-DD’)           – 2012-01-03
– CURRENT_TIMESTAMP returns current system date and time in standard internal format
SELECT dbo.fnFormatDate (CURRENT_TIMESTAMP,‘YY.MM.DD’)      – 12.01.03

Of course you can expand the function so it also can handle times

Reference:

• How to format datetime & date in Sql Server 2005

Answer 4

convert function in sqlserver gives you the format.

Here is same question asked : -

Sql Server string to date conversion

SQL Server convert string to datetime

You are not written your sqlserver version : if 2012 , then check link-

http://www.databasejournal.com/features/mssql/the-format-function-in-sql-server-2012.html

You can uset ISDATE function to check that string is valid datetime value.

Make a sp, not a function because it not handle try... catch.. block

create procedure dbo.strToDatefunction
(
    @dateString varchar(50)
)
as
BEGIN
    Declare @ConvertDateInMDYFormat datetime
    set dateformat dmy

    /*now start arbitraory string to convert and handle in try catch , so next format apply to convert*/
    BEGIN TRY
      set @ConvertDateInMDYFormat = (select @dateString,convert(varchar,@dateString,101),101,'mm/dd/yy')
    END TRY
    BEGIN CATCH      
    END CATCH

    /*apply second format you want */
    BEGIN TRY
      set @ConvertDateInMDYFormat = (select @dateString,convert(varchar,@dateString,101),103,'mm/dd/yy')
    END TRY
    BEGIN CATCH      
    END CATCH

    .... apply all format as above which possbile to give datetime as per your requirement
return @ConvertDateInMDYFormat
END