Convert date to yyyymmdd in SQL Server CE database

[EwS]

I'm using SQL Server CE database for my Pocket PC app.
My app has a date picker control which displays the date in this format: MM/DD/YYYY. I need to convert that date to YYYYMMDD and store it in this format in the database. I've been strugling with the conversion without any success. How can I accomplish that? Thank you for any help!!

 

[Joulius]

You could use a function like this one

CREATE FUNCTION [dbo].[f_timekey] (@date datetime, @precision tinyint)
RETURNS bigint
AS  
BEGIN 
DECLARE @yy char(4),@mm char(2),@dd char(2),@hh char(2),@mi char(2),@ss char(2),@date_key bigint
SET @yy = DATEPART(yy, @date)
SET @mm = CASE 	WHEN LEN(DATEPART(mm,@date)) = 1 THEN '0' + CAST(DATEPART(mm,@date) AS varchar(2)) 
		ELSE CAST(DATEPART(mm,@date) AS varchar(2))
	END
SET @dd = CASE 	WHEN LEN(DATEPART(dd,@date)) = 1 THEN '0' + CAST(DATEPART(dd,@date) AS varchar(2)) 
		ELSE CAST(DATEPART(dd,@date) AS varchar(2))
	END
SET @hh = CASE 	WHEN LEN(DATEPART(hh,@date)) = 1 THEN '0' + CAST(DATEPART(hh,@date) AS varchar(2)) 
		ELSE CAST(DATEPART(hh,@date) AS varchar(2))
	END
SET @mi = CASE 	WHEN LEN(DATEPART(mi,@date)) = 1 THEN '0' + CAST(DATEPART(mi,@date) AS varchar(2)) 
		ELSE CAST(DATEPART(mi,@date) AS varchar(2))
	END
SET @ss = CASE 	WHEN LEN(DATEPART(ss,@date)) = 1 THEN '0' + CAST(DATEPART(ss,@date) AS varchar(2)) 
		ELSE CAST(DATEPART(ss,@date) AS varchar(2))
	END
SELECT @date_key = CAST(@yy + 	CASE 	WHEN @precision = 5 THEN @mm + @dd + @hh + @mi + @ss 
					WHEN @precision = 4 THEN @mm + @dd + @hh + @mi
					WHEN @precision = 3 THEN @mm + @dd + @hh
					WHEN @precision = 2 THEN @mm + @dd
					WHEN @precision = 1 THEN @mm
				END AS bigint)
RETURN(@date_key)
END

 

[vongrunt]

Thursday is day for inliners , therefore:

create function f_timekey2( @date datetime, @precision tinyint)
returns bigint
as
begin
	return (1-nullif(abs(sign(floor((@precision-1)/5.))), 1)) *
		left(convert(varchar(8), @date, 112) + replace(convert(varchar(8), @date, 108), ':', ''), 4+2*(@precision%6))
end

Btw. this also does M/D/Y to YMD conversion:

select convert(varchar(8), convert(datetime, '12/01/2005', 101), 112)

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[Joulius]

I sugest using the datetime to numeric cast because you will still be able to use the BETWEEN condition.