I am writing a function which has a parameter @terminationMonthYear with datatype nvarchar,
i need to convert the above parameter in to datetime.
Eg : If i pass (January,2013) .I need it to convert in to first day of that particular month '2013-01-01' with datetime datatype in sql server.
Thanks in advance
if you pass "Jan,2013" it will work
@MonthName Varchar(50) Declare @FromDate datetime, @ToDate datetime Select @FromDate = Convert(Datetime,@MonthName), @ToDate = DateAdd(d,-1,DateAdd(m,1,Convert(Datetime,@MonthName))) Try this (in this example, @Date represents @terminationMonthYear):
DECLARE @Date NVARCHAR(50) = 'February ,2013' DECLARE @Month NVARCHAR(50) = LTRIM(RTRIM(LEFT(@Date, PATINDEX('%,%',@Date)-1))) DECLARE @Year NVARCHAR(50) = LTRIM(RTRIM(RIGHT(@Date, LEN(@Date) - PATINDEX('%,%',@Date)))) SELECT CAST(@Month + ' 01, ' + @Year AS DATE) OR if your input parameter includes the (), then try this:
DECLARE @Date NVARCHAR(50) = '(February ,2013)' SET @Date = REPLACE(REPLACE(@Date,'(',''),')','') DECLARE @Month NVARCHAR(50) = LTRIM(RTRIM(LEFT(@Date, PATINDEX('%,%',@Date)-1))) DECLARE @Year NVARCHAR(50) = LTRIM(RTRIM(RIGHT(@Date, LEN(@Date) - PATINDEX('%,%',@Date)))) SELECT CAST(@Month + ' 01, ' + @Year AS DATE) This will work whether you pass in the full month name or the 3-letter abbreviation (e.g. Mar for March). Also, you mentioned you wanted to convert it into DATETIME format, but 2013-01-01 is a DATE (no time component). If you want a time component, you can just change the CAST in the last line to "... AS DATETIME" and it will add a time component (though it will be all 0's).
If you know your format is always going to be "month, year" could you do the following by just replacing the , with 01,:
DECLARE @input VARCHAR(100) SET @input = 'January, 2014' DECLARE @newinput VARCHAR(100) SET @newinput = REPLACE(@input, ',', ' 01,') SELECT CAST(@newinput AS DATETIME) Or if you just want the one liner version:
SELECT CAST(REPLACE(@input, ',', ' 01,') AS DATETIME) A lot simpler but you need to make sure the format input is correct but you will probably have this issue with almost any type of parsing solution as well.
EDIT: Comment was you might need to get rid of the brackets as well so easy enough:
SELECT CAST(REPLACE(REPLACE(REPLACE(@input, ',', ' 01,'), ')', ''), '(', '') AS DATETIME) 
The convert() function will convert '01-January-2013' to a valid date. You just need to add the 01 at the beginning of your string and turn the comma into a dash:
convert(date, '01-' + replace(@terminationMonthYear, ',', '-') The SQL CAST function is surprisingly versatile:
SELECT CAST('January,2013' AS DATETIME) Returns '2013-01-01 00:00:00.000', which I believe is exactly what you were looking for. The time information seems redundant though, so consider casting it as DATE instead.
