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

Convert varchar back into datetime?? Expand / Collapse
Author
Message
Posted Wednesday, April 24, 2013 10:19 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, August 25, 2014 9:58 AM
Points: 92, Visits: 679
Hello -

I have a column in my View script that I convert to varchar(10) and want to convert it back to a date for the output to be in excel. Here is the original line

 CONVERT(VARCHAR(10), d.DocDate, 110) AS [Invoice Date]


I have tried this line with no luck.

CONVERT(VARCHAR(10), CONVERT(DATETIME, d.DocDate), 110) AS [Invoice Date]

Can someone confirm if I have the right lines in the sql liine? Or I'm open to another way of writing it out.

Thank you
David
Post #1446107
Posted Wednesday, April 24, 2013 10:27 AM


SSC-Insane

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

Group: General Forum Members
Last Login: Yesterday @ 8:19 PM
Points: 23,286, Visits: 32,012
david.ostrander (4/24/2013)
Hello -

I have a column in my View script that I convert to varchar(10) and want to convert it back to a date for the output to be in excel. Here is the original line

 CONVERT(VARCHAR(10), d.DocDate, 110) AS [Invoice Date]


I have tried this line with no luck.

CONVERT(VARCHAR(10), CONVERT(DATETIME, d.DocDate), 110) AS [Invoice Date]

Can someone confirm if I have the right lines in the sql liine? Or I'm open to another way of writing it out.

Thank you
David


First, why not just drop the convert to varchar completely. If you are using it to drop the time portion, you will get that back converting back to a datetime value (just 00:00:00.000). If you need the later as a datetime then this works:

dateadd(dd, datediff(dd, 0, d.DocDate), 0)

If you still want to go through the convert to varchar, then this:

 CONVERT(DATETIME, CONVERT(VARCHAR(10), d.DocDate, 110)) AS [Invoice Date]




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 #1446113
Posted Wednesday, April 24, 2013 12:41 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, August 25, 2014 9:58 AM
Points: 92, Visits: 679
Lynn -

Thank you for the tip.

I used
dateadd(dd, datediff(dd, 0, d.DocDate), 0)

but how do I get rid of the
"00:00:00:00"?

Thanks,
David
Post #1446183
Posted Wednesday, April 24, 2013 1:11 PM


SSC-Insane

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

Group: General Forum Members
Last Login: Yesterday @ 8:19 PM
Points: 23,286, Visits: 32,012
david.ostrander (4/24/2013)
Lynn -

Thank you for the tip.

I used
dateadd(dd, datediff(dd, 0, d.DocDate), 0)

but how do I get rid of the
"00:00:00:00"?

Thanks,
David


If you want the datatype to be datetime, you don't.

If you are exporting this data to Excel, let Excel deal with the formatting of the 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 #1446196
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse