Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

convert from MM/DD/YYYY to YYYYMMDD Expand / Collapse
Author
Message
Posted Monday, October 22, 2007 4:06 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, September 8, 2014 8:56 AM
Points: 162, Visits: 436
Hi friends,



I have a Date column as MM/DD/YYYY(datetime) i need to convert this to YYYYMMDD(int). Can anyone help me in converting this....

i tried this, but am getting an error
SELECT distinct convert(datetime,Date)as [YYYYMMDD]

error:Implicit conversion from data type datetime to int is not allowed. Use the CONVERT function to run this query.



Thanks,

baru.

Post #413605
Posted Monday, October 22, 2007 4:29 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 7:12 PM
Points: 23,397, Visits: 32,241
Can we ask why you need to do this?

Also, this should work: select year(getdate()) * 10000 + month(getdate()) * 100 + day(getdate())

Replace getdate() with your column name.




Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #413613
Posted Tuesday, October 23, 2007 8:46 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, September 8, 2014 8:56 AM
Points: 162, Visits: 436
i need to do this coz i have the Date column type as datetime and i need to use this column to populate a column in newtable which has int datatype as the data type and format YYYYMMDD....
Post #413965
Posted Tuesday, October 23, 2007 9:21 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 7:12 PM
Points: 23,397, Visits: 32,241
Hopefully the code I provided works for you, but I (and I am sure others will agree) think it is not a good idea to store dates as integer values. Dates should be stored as dates.




Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #413986
Posted Tuesday, October 23, 2007 9:32 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, September 8, 2014 8:56 AM
Points: 162, Visits: 436
ya, but my team want that to be an int value...
Post #413994
Posted Tuesday, October 23, 2007 2:34 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, September 8, 2014 8:56 AM
Points: 162, Visits: 436
hi folks,

I solved this issue...
i used this to convert from datetime to int datatype

convert(char(10),Date,112)
O/P:20030306
Post #414098
Posted Wednesday, October 24, 2007 6:32 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 4:16 AM
Points: 2,897, Visits: 5,982
bharani (10/23/2007)
ya, but my team want that to be an int value...


I don't mean to be rude, but... Dates should be stored as dates, soring them as ints will cause you all manner of problems down the road. If you're the DBA and the rest of the team are developers, stand up for your data and let them know that dates are already stored as number, the number of days/hours/seconds/etc from some date depending on the dbms.

Let them reformat it however they want in the business or presentation layer, not at the data layer.

-Luke.


To help us help you read this

For better help with performance problems please read this
Post #414377
Posted Thursday, October 25, 2007 2:42 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, June 24, 2011 9:55 AM
Points: 524, Visits: 219
you can use date Style numbers to convert to different date formats

here is an example for converting current date to different formats

select CONVERT( varchar(10), getdate(), 111 )
or
select CONVERT( varchar(10), getdate(), 112 )

where 111,112 are different data style number.

Regards,

Amit kulkarni
Post #414828
Posted Thursday, October 25, 2007 3:28 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: Banned Members
Last Login: Sunday, September 7, 2014 11:11 PM
Points: 2,622, Visits: 328
How to convert mm/dd/yyyy date format to yyyymmdd -

try this!!!!!!!!!!

www.pcreview.co.uk/forums/thread-1780991.php
Post #414837
Posted Thursday, October 25, 2007 3:36 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, September 11, 2014 1:58 AM
Points: 2,397, Visits: 3,407
Don't convert nor update!

Create a calculated column with the new datetimeformat...



N 56°04'39.16"
E 12°55'05.25"
Post #414841
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse