Author |
Topic |
masond
Constraint Violating Yak Guru
447 Posts |
Posted - 2013-06-11 : 03:28:00
|
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:01This 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
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-06-11 : 03:38:33
|
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 |
|
|
masond
Constraint Violating Yak Guru
447 Posts |
Posted - 2013-06-11 : 03:42:11
|
Hi Bandi Yes i want the date format changed to YYYY-MM-DD and then append T23:01 to each date |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-11 : 04:00:50
|
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 likeSELECT DATEADD(minute,DATEDIFF(minute,0,'23:01'),CONVERT(datetime,yourdatecolumn,112))FROM table ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
masond
Constraint Violating Yak Guru
447 Posts |
Posted - 2013-06-11 : 04:04:27
|
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:01I need to have it in the exactly same format other wise i cant put the data into our CRM tool |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-11 : 04:26:46
|
[code]SELECT CONVERT(varchar(20),DATEADD(minute,DATEDIFF(minute,0,'23:01'),CONVERT(datetime,yourdatecolumn,112)),126)FROM table[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-06-11 : 04:30:52
|
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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs
--Chandu |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-11 : 04:44:45
|
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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
masond
Constraint Violating Yak Guru
447 Posts |
Posted - 2013-06-11 : 05:04:56
|
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 |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-06-11 : 05:16:02
|
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 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-11 : 05:16:07
|
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 hereyou can just make varchar length as 16 and will get same resultI just copy pasted from pervious suggestion which is why length was kept @ 20------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2013-06-12 : 02:51:44
|
Simpler.DECLARE @Sample TABLE ( Data CHAR(8) NOT NULL );INSERT @Sample ( Data )VALUES ('20091109');-- SwePesoSELECT CONVERT(CHAR(16), DATEADD(MINUTE, 1381, Data), 126)FROM @Sample; N 56°04'39.26"E 12°55'05.63" |
|
|
|