October 2, 2014 at 9:12 am
Not sure what I'm missing, but I can't get a varchar to datetime conversion to work. The varchar data is in the form yyyymmddhhmi
I tried convert(datetime, '200508310926')
also tried cast('200508310926' as datetime) both have error "Conversion failed when converting date and/or time from character string"
Is there a format code that needs to be there? If so, I can't figure out what it should be.
I eventually need to convert these to dates and compare them to getdate, example:
...where convert(datetime, dtfield) >= getdate()-1
Thanks very much for any ideas.
October 2, 2014 at 10:06 am
Quick thought, you can do some string splitting and concatenation, not too pretty but works
😎
USE tempdb;
GO
SET NOCOUNT ON;
GO
DECLARE @DATETIMESTRING VARCHAR(12) = '200508310926';
SELECT CONVERT(DATETIME,SUBSTRING(@DATETIMESTRING,1,4) + CHAR(45) +
SUBSTRING(@DATETIMESTRING,5,2) + CHAR(45) +
SUBSTRING(@DATETIMESTRING,7,2) + CHAR(32) +
SUBSTRING(@DATETIMESTRING,9,2) + CHAR(58) +
SUBSTRING(@DATETIMESTRING,11,2),120);
Result
2005-08-31 09:26:00.000
October 2, 2014 at 10:13 am
You can format your string with STUFF(). However, I wouldn't advice to convert your column. You should convert the GETDATE()-1 to string or even better, change the column data type to the correct one.
Here's an example of how to do it both ways.
SELECT CONVERT( datetime, STUFF(STUFF('201410011112', 11, 0, ':'), 9, 0, ' ')) StringToDate,
REPLACE( REPLACE( REPLACE( CONVERT( varchar( 16), GETDATE() - 1, 120), '-', ''), ' ', ''), ':', '') DateToString1,
CONVERT( char( 8), GETDATE() - 1, 112) + REPLACE( CONVERT( char( 5), GETDATE() - 1, 14), ':', '') DateToString2
October 2, 2014 at 10:28 am
The field is coming in from another source that has all fields as varchar. I used this from your example to change getdate to char and it works good for this purpose.
...where mydatefield >= convert(char(8), getdate()-1, 112)+replace(convert(char(5), getdate()-1, 14), ':', '')
Thanks very much.
October 2, 2014 at 10:58 am
I feel your pain as I have to work in similar conditions. However, I felt that I needed to share the best option even if most times is not possible to implement it.
Glad that the solution helped you.
October 2, 2014 at 1:16 pm
Format 'YYYYMMDD hh:mm' is always accurately translated in SQL Server.
Therefore, you need just two STUFFs:
SELECT
varchar_data AS original_string,
CAST(STUFF(STUFF(varchar_data, 9, 0, ' '), 12, 0, ':') AS datetime) AS converted_to_datetime
FROM (
SELECT '200508310926' AS varchar_data
) AS test_data
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
October 2, 2014 at 1:41 pm
Awesome, that works too. I thought that it should be able to make the conversion from varchar to datetime, but just couldn't get the syntax.
Thanks very much.
October 2, 2014 at 3:15 pm
Other than using CAST instead of CONVERT, how is that different from what I posted?
I included the other way around to prevent using functions on the column used in the WHERE statement and keeping the query SARGable.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply