Creating Date out of string - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Creating Date out of string

Author

Topic

umertahir
Posting Yak Master

154 Posts

Posted - 2010-03-04 : 06:24:16

Hi All,

Could I please get some help on creating date from the following string sample:

'Date: 28.02.10 Time: 23:46'

I want to pick out the date part and present it like:
28-Feb-10

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-03-04 : 07:31:55

I'd do something like this to get it as a DATETIME (without the time portion).

Then it's easy to present it any way you want.

Here I've set the breakpoint at 51 -- so 51 = 2051 and 52 = 1952


/*
Hi All,

Could I please get some help on creating date from the following string sample:

'Date: 28.02.10 Time: 23:46'


I want to pick out the date part and present it like:
28-Feb-10
*/

DECLARE @testTable TABLE (
	[ID] INT IDENTITY(1,1) PRIMARY KEY
	, [input] VARCHAR(255)
	)

INSERT @testTable ([input])
      SELECT 'Date: 28.02.10 Time: 23:46'
UNION SELECT 'Date: 12.12.90 Time: 00:30'
UNION SELECT 'Date: 01.11.01 Time: 01:30'
UNION SELECT 'Date: 30.03.50 Time: 02:30'
UNION SELECT 'Date: 25.10.71 Time: 03:30'
UNION SELECT 'Date: 15.09.29 Time: 04:30'
UNION SELECT 'Date: 07.07.14 Time: 05:30'

SELECT * FROM @testTable

SELECT

	t.[ID]
	, t.[input] AS [Orriginal String]

	, CAST (

		-- Year
		CASE
			WHEN CAST(PARSENAME([dateSlice], 1) AS INT) > 51 THEN '19'
			ELSE '20'
		END

		+ PARSENAME([dateSlice], 1)

		-- Month
		+ PARSENAME([dateSlice], 2)

		-- Day
		+ PARSENAME([dateSlice], 3)

	AS DATETIME ) AS [Formatted Date]
FROM
	(
	SELECT
		[ID] AS [Id]
		, [input] AS [Input]
		, SUBSTRING([Input], 7, 8) AS [dateSlice]
	FROM
		@testTable
	)
	t

It would be better to change your input to something other than that horrible string though.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-04 : 07:35:37



DECLARE @testTable TABLE (
	[ID] INT IDENTITY(1,1) PRIMARY KEY
	, [input] VARCHAR(255)
	)

INSERT @testTable ([input])
      SELECT 'Date: 28.02.10 Time: 23:46'
UNION SELECT 'Date: 12.12.90 Time: 00:30'
UNION SELECT 'Date: 01.11.01 Time: 01:30'
UNION SELECT 'Date: 30.03.50 Time: 02:30'
UNION SELECT 'Date: 25.10.71 Time: 03:30'
UNION SELECT 'Date: 15.09.29 Time: 04:30'
UNION SELECT 'Date: 07.07.14 Time: 05:30'

SELECT * FROM @testTable
set dateformat dmy

SELECT  [ID]
	, [input] AS [Orriginal String]
	,cast(substring([input],charindex('Date: ',[input])+6,9) as datetime) FROM @testTable

Madhivanan

Failing to plan is Planning to fail

umertahir
Posting Yak Master

154 Posts

Posted - 2010-03-04 : 08:29:07

Thanks, that works. I will remember to put dateformat as dmy in the start as well.

quote:

Originally posted by madhivanan

or



DECLARE @testTable TABLE (
	[ID] INT IDENTITY(1,1) PRIMARY KEY
	, [input] VARCHAR(255)
	)

INSERT @testTable ([input])
      SELECT 'Date: 28.02.10 Time: 23:46'
UNION SELECT 'Date: 12.12.90 Time: 00:30'
UNION SELECT 'Date: 01.11.01 Time: 01:30'
UNION SELECT 'Date: 30.03.50 Time: 02:30'
UNION SELECT 'Date: 25.10.71 Time: 03:30'
UNION SELECT 'Date: 15.09.29 Time: 04:30'
UNION SELECT 'Date: 07.07.14 Time: 05:30'

SELECT * FROM @testTable
set dateformat dmy

SELECT  [ID]
	, [input] AS [Orriginal String]
	,cast(substring([input],charindex('Date: ',[input])+6,9) as datetime) FROM @testTable

Madhivanan

Failing to plan is Planning to fail

umertahir
Posting Yak Master

154 Posts

Posted - 2010-03-04 : 08:39:11

Just to add to the code as I wanted date in DD-MON-YY format:


DECLARE @testTable TABLE (
	[ID] INT IDENTITY(1,1) PRIMARY KEY
	, [input] VARCHAR(255)
	)

INSERT @testTable ([input])
      SELECT 'Date: 28.02.10 Time: 23:46'
UNION SELECT 'Date: 12.12.90 Time: 00:30'
UNION SELECT 'Date: 01.11.01 Time: 01:30'
UNION SELECT 'Date: 30.03.50 Time: 02:30'
UNION SELECT 'Date: 25.10.71 Time: 03:30'
UNION SELECT 'Date: 15.09.29 Time: 04:30'
UNION SELECT 'Date: 07.07.14 Time: 05:30'

SELECT * FROM @testTable
set dateformat dmy

SELECT  [ID],
		[input] AS [Orriginal String],
		cast(substring([input], charindex('Date: ',[input])+6,9) as datetime) AS ConvertedDateTime,
		REPLACE(CONVERT(VARCHAR(9), cast(substring([input], charindex('Date: ',[input])+6,9) as datetime), 6), ' ', '-') AS [DD-Mon-YY]
FROM @testTable

OutPut:


ID	Orriginal String	      ConvertedDateTime    DD-Mon-YY
1	Date: 01.11.01 Time: 01:30	2001-11-01 00:00:00.000	01-Nov-01
2	Date: 07.07.14 Time: 05:30	2014-07-07 00:00:00.000	07-Jul-14
3	Date: 12.12.90 Time: 00:30	1990-12-12 00:00:00.000	12-Dec-90
4	Date: 15.09.29 Time: 04:30	2029-09-15 00:00:00.000	15-Sep-29
5	Date: 25.10.71 Time: 03:30	1971-10-25 00:00:00.000	25-Oct-71
6	Date: 28.02.10 Time: 23:46	2010-02-28 00:00:00.000	28-Feb-10
7	Date: 30.03.50 Time: 02:30	1950-03-30 00:00:00.000	30-Mar-50

Thanks a lot for the help guys.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-04 : 08:54:57

quote:

Originally posted by umertahir

Just to add to the code as I wanted date in DD-MON-YY format:


DECLARE @testTable TABLE (
	[ID] INT IDENTITY(1,1) PRIMARY KEY
	, [input] VARCHAR(255)
	)

INSERT @testTable ([input])
      SELECT 'Date: 28.02.10 Time: 23:46'
UNION SELECT 'Date: 12.12.90 Time: 00:30'
UNION SELECT 'Date: 01.11.01 Time: 01:30'
UNION SELECT 'Date: 30.03.50 Time: 02:30'
UNION SELECT 'Date: 25.10.71 Time: 03:30'
UNION SELECT 'Date: 15.09.29 Time: 04:30'
UNION SELECT 'Date: 07.07.14 Time: 05:30'

SELECT * FROM @testTable
set dateformat dmy

SELECT  [ID],
		[input] AS [Orriginal String],
		cast(substring([input], charindex('Date: ',[input])+6,9) as datetime) AS ConvertedDateTime,
		REPLACE(CONVERT(VARCHAR(9), cast(substring([input], charindex('Date: ',[input])+6,9) as datetime), 6), ' ', '-') AS [DD-Mon-YY]
FROM @testTable

OutPut:


ID	Orriginal String	      ConvertedDateTime    DD-Mon-YY
1	Date: 01.11.01 Time: 01:30	2001-11-01 00:00:00.000	01-Nov-01
2	Date: 07.07.14 Time: 05:30	2014-07-07 00:00:00.000	07-Jul-14
3	Date: 12.12.90 Time: 00:30	1990-12-12 00:00:00.000	12-Dec-90
4	Date: 15.09.29 Time: 04:30	2029-09-15 00:00:00.000	15-Sep-29
5	Date: 25.10.71 Time: 03:30	1971-10-25 00:00:00.000	25-Oct-71
6	Date: 28.02.10 Time: 23:46	2010-02-28 00:00:00.000	28-Feb-10
7	Date: 30.03.50 Time: 02:30	1950-03-30 00:00:00.000	30-Mar-50

Thanks a lot for the help guys.

If you use front end application to display data, use format function there

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources