How to Set a Datetime Variable in SQL Server 2008

How to set datetime variable in SQL Server 2008

declare @test datetime

set @test='03/21/2014'

//set @test='21/03/2014' This is not valid you must give date in MM/dd/yyyy

print @test

set @test='2014/03/21'

print @test

Add X number of years to a datetime variable SQL Server 2008

In SQL Server 2008 all queries are part of a transaction that is implicitly committed. I'm assuming you are okay with using the dateadd function, what you have looks correct. So to modify to allow rollback you could do the following - although not sure when / why you would rollback, that logic would need to be added.

BEGIN TRANSACTION addDateYears
update myTable
set admitdate=dateadd(yyyy,10,admitdate)
where visitYear='2010'

/* IF SOMETHING ROLLBACK TRANSACTION addDateYears */

COMMIT TRANSACTION addDateYears

Change a datetime variable to be only the year of the variable

You could probably just add a new computed column:

ALTER TABLE tempdb.dbo.DataAvailableForAnalysis
  ADD DobYear AS YEAR(DOB) PERSISTED;

and then you have both the actual complete DOB as well as the DobYear column available

How to add time to DateTime in SQL

Try this

SELECT DATEADD(day, DATEDIFF(day, 0, GETDATE()), '03:30:00')

Issue with datetime variable in sql server 2008

Remove GO from your script; the GO is essentially starting a new section of processing, and @ShiftDate won't be in scope in the next section of your script.

See the MSDN documentation on GO for more information.

SQL query to insert datetime in SQL Server

You will want to use the YYYYMMDD for unambiguous date determination in SQL Server.

insert into table1(approvaldate)values('20120618 10:34:09 AM');

If you are married to the dd-mm-yy hh:mm:ss xm format, you will need to use CONVERT with the specific style.

insert into table1 (approvaldate)
       values (convert(datetime,'18-06-12 10:34:09 PM',5));

5 here is the style for Italian dates. Well, not just Italians, but that's the culture it's attributed to in Books Online.

Declaring a datetime variable

Demo

Note you will get the last date read and there is no natural order so you need a unique sort to get a good assignment.

declare @dates table(iden int identity primary key, d1 datetime, d2 datetime) 
insert into @dates values ('1/1/1910', '1/2/1910'), ('1/1/1915', '1/2/1915');
DECLARE @StartDate DATETIME = (select top (1) d.d1 from @dates d);
DECLARE @EndDate   DATETIME;

SELECT @StartDate = d.d1, @EndDate = d.d2 
FROM @dates d;

select @StartDate, @EndDate 

SELECT top (1) @StartDate = d.d1, @EndDate = d.d2 
FROM @dates d 
order by d.iden;

select @StartDate, @EndDate 

----------------------- -----------------------
1915-01-01 00:00:00.000 1915-01-02 00:00:00.000             
----------------------- -----------------------
1910-01-01 00:00:00.000 1910-01-02 00:00:00.000

In the declare line you need to use ( ) and only return 1

Set time portion of a datetime variable

DECLARE @start_date DATETIME
DECLARE @end_date DATETIME

SET @start_date = DATEADD(hour, 20, DATEDIFF(DAY, 2, GETDATE()))
SET @end_date = @start_date + 1

select @start_date, @end_date

Set time part of datetime variable to 18:00

SELECT DATEADD(hh, 24 * 2 + 18, DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0))

This truncates the current date and adds 2 days and 18 hours to it (24 * 2 + 18).

A possible variation:

SELECT DATEADD(hh, 18, DATEADD(dd, DATEDIFF(dd, -2, GETDATE()), 0))

---

Related Topics