dateserial eqivalent

26 Jan 2006 8:29 PM

Erez Mor

hi all
what is an equivalent to VB's dateserial (if any)?
thanx,
erez

26 Jan 2006 8:41 PM

Patrik

Not sure what you want to do, but you can use convert.

For example

select convert(datetime, '2006-01-26')

26 Jan 2006 8:46 PM

David Portas

Erez Mor wrote:

> hi all
> what is an equivalent to VB's dateserial (if any)?
> thanx,
> erez

DateSerial takes three numeric arguments for year, month and day and
turns them into a date. There probably aren't many situations in SQL
Server when it would make sense to manipulate dates as three separate
numeric values. The easiest way to specify dates in code is as a
string:

SELECT CAST('20060126' AS DATETIME);

Always store dates as DATETIME or SMALLDATETIME.

If you need to do date arithmetic, use the DATEADD and DATEDIFF
functions.

If you still think you have a need to use three numerics for year,
month and day, try this:

SELECT
CAST(
RIGHT('0000'+CAST(2006 AS VARCHAR(4)),4)+
RIGHT('00'+CAST(1 AS VARCHAR(2)),2)+
RIGHT('00'+CAST(26 AS VARCHAR(2)),2)
AS DATETIME);

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

26 Jan 2006 8:55 PM

Erez Mor

thanx a lot, folks

Show quote

"David Portas" wrote:

> Erez Mor wrote:
> > hi all
> > what is an equivalent to VB's dateserial (if any)?
> > thanx,
> > erez
>
> DateSerial takes three numeric arguments for year, month and day and
> turns them into a date. There probably aren't many situations in SQL
> Server when it would make sense to manipulate dates as three separate
> numeric values. The easiest way to specify dates in code is as a
> string:
>
> SELECT CAST('20060126' AS DATETIME);
>
> Always store dates as DATETIME or SMALLDATETIME.
>
> If you need to do date arithmetic, use the DATEADD and DATEDIFF
> functions.
>
> If you still think you have a need to use three numerics for year,
> month and day, try this:
>
> SELECT
> CAST(
>   RIGHT('0000'+CAST(2006 AS VARCHAR(4)),4)+
>   RIGHT('00'+CAST(1 AS VARCHAR(2)),2)+
>   RIGHT('00'+CAST(26 AS VARCHAR(2)),2)
>   AS DATETIME);
>
> --
> David Portas, SQL Server MVP
>
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
>
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>
>