SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Date function Help
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

masond
Constraint Violating Yak Guru

447 Posts

Posted - 06/11/2013 :  03:28:00  Show Profile  Reply with Quote
Hey Guys

This is a really stupid question but this has stumped me
In my table i currently have the date displayed as “20091109”
however I need the date displayed as 2009-11-09T23:01

This is my table
SELECT
[First_Post_Date]
,[Last_Post_Date]
FROM [FDMS].[dbo].[Dim_Outlet]
And this is the datatype
[First_Post_Date] Data Type – Varchar8
[Last_Post_Date] Data Type – Varchar8

Any help would be much appreciated

bandi
Flowing Fount of Yak Knowledge

India
2224 Posts

Posted - 06/11/2013 :  03:38:33  Show Profile  Reply with Quote
hi masond,
You have varchar(8) for those date columns....
How can you have this time part "T23:01"?
means you want YYYY-MM-DD format and then appending T23:01 for each date ?


--
Chandu
Go to Top of Page

masond
Constraint Violating Yak Guru

447 Posts

Posted - 06/11/2013 :  03:42:11  Show Profile  Reply with Quote
Hi Bandi

Yes i want the date format changed to YYYY-MM-DD and then append T23:01 to each date
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 06/11/2013 :  04:00:50  Show Profile  Reply with Quote
quote:
Originally posted by masond

Hi Bandi

Yes i want the date format changed to YYYY-MM-DD and then append T23:01 to each date



just do like

SELECT DATEADD(minute,DATEDIFF(minute,0,'23:01'),CONVERT(datetime,yourdatecolumn,112))
FROM table


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

masond
Constraint Violating Yak Guru

447 Posts

Posted - 06/11/2013 :  04:04:27  Show Profile  Reply with Quote
Visakh16

If i do your method the data returned is

2005-02-02 23:01:00.000

I need the data returned like this 2005-02-02T23:01

I need to have it in the exactly same format other wise i cant put the data into our CRM tool
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 06/11/2013 :  04:26:46  Show Profile  Reply with Quote

SELECT CONVERT(varchar(20),DATEADD(minute,DATEDIFF(minute,0,'23:01'),CONVERT(datetime,yourdatecolumn,112)),126)
FROM table



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2224 Posts

Posted - 06/11/2013 :  04:30:52  Show Profile  Reply with Quote
You can get exact YYYYMMDDTHH:MI by using VARCHAR(16)...

quote:
Originally posted by visakh16


SELECT CONVERT(varchar(16),DATEADD(minute,DATEDIFF(minute,0,'23:01'),CONVERT(datetime,yourdatecolumn,112)),126)
FROM table



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs




--
Chandu
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 06/11/2013 :  04:44:45  Show Profile  Reply with Quote
And as always better to do this at front end unless you dont have a way out. Try to keep date values as date type itself in SQL. otherwise it will cause issues if require any further manipulation with them.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

masond
Constraint Violating Yak Guru

447 Posts

Posted - 06/11/2013 :  05:04:56  Show Profile  Reply with Quote
Hi Guys

Thank you for your response

the way i got around it was
left (CONVERT(varchar(20),DATEADD(minute,DATEDIFF(minute,0,'23:01'),CONVERT(datetime,[Open_Date],112)),126),16)as Open_Date,

The left function is doing exactly the same as the varchar 16 :)

So it looks like we all on the same wave length
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2224 Posts

Posted - 06/11/2013 :  05:16:02  Show Profile  Reply with Quote
quote:
Originally posted by masond

Hi Guys

Thank you for your response

the way i got around it was
left (CONVERT(varchar(20),DATEADD(minute,DATEDIFF(minute,0,'23:01'),CONVERT(datetime,[Open_Date],112)),126),16)as Open_Date,

The left function is doing exactly the same as the varchar 16 :)

So it looks like we all on the same wave length



No need of LEFT Scalar function again....
It is enough...
CONVERT(varchar(16),DATEADD(minute,DATEDIFF(minute,0,'23:01'),CONVERT(datetime,[Open_Date],112)),126)as Open_Date

--
Chandu
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 06/11/2013 :  05:16:07  Show Profile  Reply with Quote
quote:
Originally posted by masond

Hi Guys

Thank you for your response

the way i got around it was
left (CONVERT(varchar(20),DATEADD(minute,DATEDIFF(minute,0,'23:01'),CONVERT(datetime,[Open_Date],112)),126),16)as Open_Date,

The left function is doing exactly the same as the varchar 16 :)

So it looks like we all on the same wave length


No need of additional left function usage here

you can just make varchar length as 16 and will get same result
I just copy pasted from pervious suggestion which is why length was kept @ 20

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30282 Posts

Posted - 06/12/2013 :  02:51:44  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Simpler.
DECLARE	@Sample TABLE
	(
		Data CHAR(8) NOT NULL
	);

INSERT	@Sample
	(
		Data
	)
VALUES	('20091109');

-- SwePeso
SELECT	CONVERT(CHAR(16), DATEADD(MINUTE, 1381, Data), 126)
FROM	@Sample;



N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000