Date function Help - 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

General SQL Server Forums

New to SQL Server Programming

Date function Help

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

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:01

I 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 MVP
http://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 MVP
http://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 MVP
http://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 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

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

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

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources