SQL DATE Format - 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

SQL DATE Format

Author

Topic

w1102157
Yak Posting Veteran

80 Posts

Posted - 2012-01-09 : 11:27:53

Hi Guys just got a task at work i am selecting data into a new table i have a date field which i am selecting , it is in INT and set as yyyymmdd howevere i want it dd/mm/yyyy , how can i do thsi i have tried the convertvarchar way it doesnt work please help

thank you

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-09 : 11:40:05

its a formatting problem and should be done at front end. you shouldnt be concerned on how dates are stored in db as you can always display them in format you want

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

w1102157
Yak Posting Veteran

80 Posts

Posted - 2012-01-09 : 11:55:13

the database is made by an external company , so that is out of my hand this report the boss wants the dates to show as dd/mm/yyyy can i use any string function to do this i manager to get the dd and yyyy but i dont know what function i can use to get the mm has it is in the middle the substring wont work becuase it is an INT any tips?

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-01-09 : 12:13:27

There are a bunch of ways to convert an INT to a DATE(TIME). If you like you can look at the CAST/CONVERT sql functions or do a search for how to do it. Other ways involve more "complex" ways, but they might be faster as they are more math based and less conversion based. At any rate, here is one way to do it:

DECLARE @foo int
SET @foo = 20070317
SELECT CONVERT(CHAR(10), CONVERT(DATETIME, CAST(@foo AS CHAR(8)), 112), 103)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-09 : 12:13:44

quote:
Originally posted by w1102157

the database is made by an external company , so that is out of my hand this report the boss wants the dates to show as dd/mm/yyyy can i use any string function to do this i manager to get the dd and yyyy but i dont know what function i can use to get the mm has it is in the middle the substring wont work becuase it is an INT any tips?

which reporting tool you're using?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

X002548
Not Just a Number

15586 Posts

Posted - 2012-01-09 : 12:18:39

And it's not to say that your int datatype (usually the bozo's use varchar) doesn't have an invalid date

Do this

SELECT COUNT(*) FROM yourTable
WHERE ISDATE(yourCol) = 0

And tell us what you get

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

w1102157
Yak Posting Veteran

80 Posts

Posted - 2012-01-09 : 16:21:28

hi guys thank you for all your reply, brett i am not in office so i wil have to do this tomarrow, is there a simple way of doing this for example using the right or left string function?

X002548
Not Just a Number

15586 Posts

Posted - 2012-01-09 : 17:01:38

No...you have to CONVERT it to a CHAR first to use string functions

But don't you want to work with it as a date?

Lamprey gave you the answer btw

My GUESS is that you will have garbage on some on the rows that WON'T convert to datetime

Hence my original post

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

w1102157
Yak Posting Veteran

80 Posts

Posted - 2012-01-09 : 18:19:28

I don't know how to deploy what lamprey has said I will do the query tomarrow brett and get back to you

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-10 : 03:55:40

quote:
Originally posted by w1102157

I don't know how to deploy what lamprey has said I will do the query tomarrow brett and get back to you

still i feel like you can very easily do this at front end (your reporting tool) using formatting functions

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

w1102157
Yak Posting Veteran

80 Posts

Posted - 2012-01-10 : 04:50:02

Yes i know i can as i am using excel but i they want it done in the view i am creating

and brett when i run SELECT COUNT(*) FROM yourTable
WHERE ISDATE(yourCol) = 0

the result is zero

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-10 : 05:06:50

quote:
Originally posted by w1102157

Yes i know i can as i am using excel but i they want it done in the view i am creating

and brett when i run SELECT COUNT(*) FROM yourTable
WHERE ISDATE(yourCol) = 0

the result is zero

that means all the date values stored in field are valid
just add this one check too to ensure you dont have incomplete date formats

SELECT COUNT(*) FROM yourTable
WHERE ISDATE(yourCol) = 0
AND len(yourcol)<8

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

w1102157
Yak Posting Veteran

80 Posts

Posted - 2012-01-10 : 06:56:49

Hi Visakh16

i have just carriedf out the following

SELECT COUNT(*) FROM yourTable
WHERE ISDATE(yourCol) = 0
AND len(yourcol)<8

the result again was zero

what now ?

thank you so much for your help

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-01-10 : 07:08:05

I know I am coming to the party late, but doesn't what Lamprey proposed work for you?

SELECT
    CONVERT(VARCHAR(12), CAST(CAST(YourIntColHere AS VARCHAR(8)) AS DATETIME),103)
FROM 
   yourTable;

X002548
Not Just a Number

15586 Posts

Posted - 2012-01-10 : 09:25:33

quote:
Originally posted by sunitabeck

I know I am coming to the party late

That's cause your a fashionista

And hell yes it works..can you cut and paste this and run it in SQL Server Management Studio?


DECLARE @d int; SET @d = '20010911'
SELECT CONVERT(VARCHAR(12), CAST(CAST(@d AS VARCHAR(8)) AS DATETIME),103)

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-10 : 09:32:33

quote:
Originally posted by w1102157

Hi Visakh16

i have just carriedf out the following

SELECT COUNT(*) FROM yourTable
WHERE ISDATE(yourCol) = 0
AND len(yourcol)<8

the result again was zero

what now ?

thank you so much for your help

ok. that does reiterate that all your date values are perfect

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

w1102157
Yak Posting Veteran

80 Posts

Posted - 2012-01-10 : 17:21:00

so what now visakhm??

thanks you everyone for your help

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-11 : 01:20:40

then you can use CONVERT syntax itself as others have already posted

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

w1102157
Yak Posting Veteran

80 Posts

Posted - 2012-01-11 : 07:33:39

i have and it works thank you again

X002548
Not Just a Number

15586 Posts

Posted - 2012-01-11 : 09:31:56

water closet

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources