CODEWITHSUNDEEP
sqlsql-serversql-server-2008
SqlLearner
SqlLearner

Reputation: 793

How to Convert (Monthname,year) in to DateTime Format

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

Upvotes: 2

Views: 4139

Answers (5)

Ben
Ben

Reputation: 1

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.

Upvotes: 0

PaloDravecky
PaloDravecky

Reputation: 281

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

Upvotes: 0

Mimi
Mimi

Reputation: 389

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

Upvotes: 1

Kelsey
Kelsey

Reputation: 47766

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)

Upvotes: 0

How 'bout a Fresca
How 'bout a Fresca

Reputation: 2317

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

Upvotes: 0

Related Questions