CODEWITHSUNDEEP
sql-server-2008t-sqlvarchardate-conversion
user2380911
user2380911

Reputation: 1

Convert character string to date in TSQL

I need to convert character string to date format to fetch the first and last day of the month.

Input.

DECLARE @InpDat AS VARCHAR(20) SET @InpDat = 201308

Expected output 2013-08-31

I need to get the first and last day of the given yearmonth. Can you please help me to get that.

I tried with the convert option but couldnt get it. ?

Upvotes: 0

Views: 1193

Answers (3)

PaulProgrammer
PaulProgrammer

Reputation: 17630

Why do you expect that a random number will convert to a date format without any cajoling? The database is a powerful tool but not magical.

You have to parse the number into a date, then format the date the way you like. I might suggest this sequence of calls:

-- assume you can separate the year and month into separate parts.
-- use 01 as default day for now, we'll get last day shortly.
DECLARE @IntermediateDate AS DATE SET @IntermediateDate = DATEFROMPARTS(2013,08,01)
-- Advance the month by one, and subtract one day to find the last day of the month.
SET @IntermediateDate = DATEADD( dd, -1, DATEADD( mm, 1, @IntermediateDate))

Now format using your favorite formatting functions.

Upvotes: 0

Tim Schmelter
Tim Schmelter

Reputation: 460108

Use CAST(@InpDat + '01' AS DATE) to convert it to a date and DateAdd for the date arithmetic.

DECLARE @InpDat AS VARCHAR(20) SET @InpDat = '201308'
DECLARE @Month DATE SET @Month = CAST(@InpDat + '01' AS DATE)

SELECT @MONTH AS First, 
       DATEADD(day, -1, DATEADD(month, 1, @MONTH)) AS Last

DEMO

Upvotes: 1

Giannis Paraskevopoulos
Giannis Paraskevopoulos

Reputation: 18411

DECLARE @InpDat AS VARCHAR(20) 
SET @InpDat = '201308'

SELECT CONVERT(DATETIME(@InpDat+'01') AS FirstDate,
       DATEADD(dd,-1,DATEADD(mm,1,CONVERT(DATETIME(@InpDat+'01'))) AS LastDate

Upvotes: 0

Related Questions