What ius the synthax to backup an SQLServer DB from the command line ?
ThanksUse the OSQL utility. Type:
OSQL h
at the command line to get a list of parameters for specifying a server, database, login, etc. You can use the OSQL utility to run a simple BACKUP command, or you can specify a batch file of SQL commands to perform your maintenance.sql
Showing posts with label command. Show all posts
Showing posts with label command. Show all posts
Thursday, March 29, 2012
Wednesday, March 21, 2012
DateTime question
I'm trying to update a datetime field in my database with the current
date and time. The following command inserts the date as 1/13/06 when
most systems recognize 38728 as 1/11/06. Why does sql server 2005
increase this value by two days. The code that uses this database is
written in C# and makes heavy use of DateTime especially DateTime.Now,
which thinks 38728 is 1/11/06 so I'm not looking for a different way to
add this record, I want sql server to understand that 38728 is 1/11/06
and not 1/13/06.
Please help!
INSERT INTO [dbo].[Test]
([EID]
,[modifiedBy]
,[modifiedOn])
VALUES
(9999,
'TestHarness',
38728)What result does the following give you :-
select @.@.datefirst
Try SET @.@.DateFirst (two less than the result from above) before running
your insert
--
HTH. Ryan
"BetaD" <dhorth@.horth.com> wrote in message
news:1136994628.541049.108210@.g44g2000cwa.googlegroups.com...
> I'm trying to update a datetime field in my database with the current
> date and time. The following command inserts the date as 1/13/06 when
> most systems recognize 38728 as 1/11/06. Why does sql server 2005
> increase this value by two days. The code that uses this database is
> written in C# and makes heavy use of DateTime especially DateTime.Now,
> which thinks 38728 is 1/11/06 so I'm not looking for a different way to
> add this record, I want sql server to understand that 38728 is 1/11/06
> and not 1/13/06.
> Please help!
> INSERT INTO [dbo].[Test]
> ([EID]
> ,[modifiedBy]
> ,[modifiedOn])
> VALUES
> (9999,
> 'TestHarness',
> 38728)
>|||You don't use the product correctly. You express datetimes in SQL Server as a string, not as a
number. Unfortunately, SQL Server accepts a number (implicit datatype conversion) and thereby
exposes the internals of the product. And that happens to be different from some other systems, as
you have noticed. You cannot change the behavior in this regard. Check out:
http://www.karaszi.com/SQLServer/info_datetime.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"BetaD" <dhorth@.horth.com> wrote in message
news:1136994628.541049.108210@.g44g2000cwa.googlegroups.com...
> I'm trying to update a datetime field in my database with the current
> date and time. The following command inserts the date as 1/13/06 when
> most systems recognize 38728 as 1/11/06. Why does sql server 2005
> increase this value by two days. The code that uses this database is
> written in C# and makes heavy use of DateTime especially DateTime.Now,
> which thinks 38728 is 1/11/06 so I'm not looking for a different way to
> add this record, I want sql server to understand that 38728 is 1/11/06
> and not 1/13/06.
> Please help!
> INSERT INTO [dbo].[Test]
> ([EID]
> ,[modifiedBy]
> ,[modifiedOn])
> VALUES
> (9999,
> 'TestHarness',
> 38728)
>|||Why not just take the c# datetime and convert to SqlDateTime and store that?
--
William Stacey [MVP]
"BetaD" <dhorth@.horth.com> wrote in message
news:1136994628.541049.108210@.g44g2000cwa.googlegroups.com...
> I'm trying to update a datetime field in my database with the current
> date and time. The following command inserts the date as 1/13/06 when
> most systems recognize 38728 as 1/11/06. Why does sql server 2005
> increase this value by two days. The code that uses this database is
> written in C# and makes heavy use of DateTime especially DateTime.Now,
> which thinks 38728 is 1/11/06 so I'm not looking for a different way to
> add this record, I want sql server to understand that 38728 is 1/11/06
> and not 1/13/06.
> Please help!
> INSERT INTO [dbo].[Test]
> ([EID]
> ,[modifiedBy]
> ,[modifiedOn])
> VALUES
> (9999,
> 'TestHarness',
> 38728)
>sql
date and time. The following command inserts the date as 1/13/06 when
most systems recognize 38728 as 1/11/06. Why does sql server 2005
increase this value by two days. The code that uses this database is
written in C# and makes heavy use of DateTime especially DateTime.Now,
which thinks 38728 is 1/11/06 so I'm not looking for a different way to
add this record, I want sql server to understand that 38728 is 1/11/06
and not 1/13/06.
Please help!
INSERT INTO [dbo].[Test]
([EID]
,[modifiedBy]
,[modifiedOn])
VALUES
(9999,
'TestHarness',
38728)What result does the following give you :-
select @.@.datefirst
Try SET @.@.DateFirst (two less than the result from above) before running
your insert
--
HTH. Ryan
"BetaD" <dhorth@.horth.com> wrote in message
news:1136994628.541049.108210@.g44g2000cwa.googlegroups.com...
> I'm trying to update a datetime field in my database with the current
> date and time. The following command inserts the date as 1/13/06 when
> most systems recognize 38728 as 1/11/06. Why does sql server 2005
> increase this value by two days. The code that uses this database is
> written in C# and makes heavy use of DateTime especially DateTime.Now,
> which thinks 38728 is 1/11/06 so I'm not looking for a different way to
> add this record, I want sql server to understand that 38728 is 1/11/06
> and not 1/13/06.
> Please help!
> INSERT INTO [dbo].[Test]
> ([EID]
> ,[modifiedBy]
> ,[modifiedOn])
> VALUES
> (9999,
> 'TestHarness',
> 38728)
>|||You don't use the product correctly. You express datetimes in SQL Server as a string, not as a
number. Unfortunately, SQL Server accepts a number (implicit datatype conversion) and thereby
exposes the internals of the product. And that happens to be different from some other systems, as
you have noticed. You cannot change the behavior in this regard. Check out:
http://www.karaszi.com/SQLServer/info_datetime.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"BetaD" <dhorth@.horth.com> wrote in message
news:1136994628.541049.108210@.g44g2000cwa.googlegroups.com...
> I'm trying to update a datetime field in my database with the current
> date and time. The following command inserts the date as 1/13/06 when
> most systems recognize 38728 as 1/11/06. Why does sql server 2005
> increase this value by two days. The code that uses this database is
> written in C# and makes heavy use of DateTime especially DateTime.Now,
> which thinks 38728 is 1/11/06 so I'm not looking for a different way to
> add this record, I want sql server to understand that 38728 is 1/11/06
> and not 1/13/06.
> Please help!
> INSERT INTO [dbo].[Test]
> ([EID]
> ,[modifiedBy]
> ,[modifiedOn])
> VALUES
> (9999,
> 'TestHarness',
> 38728)
>|||Why not just take the c# datetime and convert to SqlDateTime and store that?
--
William Stacey [MVP]
"BetaD" <dhorth@.horth.com> wrote in message
news:1136994628.541049.108210@.g44g2000cwa.googlegroups.com...
> I'm trying to update a datetime field in my database with the current
> date and time. The following command inserts the date as 1/13/06 when
> most systems recognize 38728 as 1/11/06. Why does sql server 2005
> increase this value by two days. The code that uses this database is
> written in C# and makes heavy use of DateTime especially DateTime.Now,
> which thinks 38728 is 1/11/06 so I'm not looking for a different way to
> add this record, I want sql server to understand that 38728 is 1/11/06
> and not 1/13/06.
> Please help!
> INSERT INTO [dbo].[Test]
> ([EID]
> ,[modifiedBy]
> ,[modifiedOn])
> VALUES
> (9999,
> 'TestHarness',
> 38728)
>sql
DateTime question
I'm trying to update a datetime field in my database with the current
date and time. The following command inserts the date as 1/13/06 when
most systems recognize 38728 as 1/11/06. Why does sql server 2005
increase this value by two days. The code that uses this database is
written in C# and makes heavy use of DateTime especially DateTime.Now,
which thinks 38728 is 1/11/06 so I'm not looking for a different way to
add this record, I want sql server to understand that 38728 is 1/11/06
and not 1/13/06.
Please help!
INSERT INTO [dbo].[Test]
([EID]
,[modifiedBy]
,[modifiedOn])
VALUES
(9999,
'TestHarness',
38728)What result does the following give you :-
select @.@.datefirst
Try SET @.@.DateFirst (two less than the result from above) before running
your insert
HTH. Ryan
"BetaD" <dhorth@.horth.com> wrote in message
news:1136994628.541049.108210@.g44g2000cwa.googlegroups.com...
> I'm trying to update a datetime field in my database with the current
> date and time. The following command inserts the date as 1/13/06 when
> most systems recognize 38728 as 1/11/06. Why does sql server 2005
> increase this value by two days. The code that uses this database is
> written in C# and makes heavy use of DateTime especially DateTime.Now,
> which thinks 38728 is 1/11/06 so I'm not looking for a different way to
> add this record, I want sql server to understand that 38728 is 1/11/06
> and not 1/13/06.
> Please help!
> INSERT INTO [dbo].[Test]
> ([EID]
> ,[modifiedBy]
> ,[modifiedOn])
> VALUES
> (9999,
> 'TestHarness',
> 38728)
>|||You don't use the product correctly. You express datetimes in SQL Server as
a string, not as a
number. Unfortunately, SQL Server accepts a number (implicit datatype conver
sion) and thereby
exposes the internals of the product. And that happens to be different from
some other systems, as
you have noticed. You cannot change the behavior in this regard. Check out:
http://www.karaszi.com/SQLServer/info_datetime.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"BetaD" <dhorth@.horth.com> wrote in message
news:1136994628.541049.108210@.g44g2000cwa.googlegroups.com...
> I'm trying to update a datetime field in my database with the current
> date and time. The following command inserts the date as 1/13/06 when
> most systems recognize 38728 as 1/11/06. Why does sql server 2005
> increase this value by two days. The code that uses this database is
> written in C# and makes heavy use of DateTime especially DateTime.Now,
> which thinks 38728 is 1/11/06 so I'm not looking for a different way to
> add this record, I want sql server to understand that 38728 is 1/11/06
> and not 1/13/06.
> Please help!
> INSERT INTO [dbo].[Test]
> ([EID]
> ,[modifiedBy]
> ,[modifiedOn])
> VALUES
> (9999,
> 'TestHarness',
> 38728)
>|||Why not just take the c# datetime and convert to SqlDateTime and store that?
William Stacey [MVP]
"BetaD" <dhorth@.horth.com> wrote in message
news:1136994628.541049.108210@.g44g2000cwa.googlegroups.com...
> I'm trying to update a datetime field in my database with the current
> date and time. The following command inserts the date as 1/13/06 when
> most systems recognize 38728 as 1/11/06. Why does sql server 2005
> increase this value by two days. The code that uses this database is
> written in C# and makes heavy use of DateTime especially DateTime.Now,
> which thinks 38728 is 1/11/06 so I'm not looking for a different way to
> add this record, I want sql server to understand that 38728 is 1/11/06
> and not 1/13/06.
> Please help!
> INSERT INTO [dbo].[Test]
> ([EID]
> ,[modifiedBy]
> ,[modifiedOn])
> VALUES
> (9999,
> 'TestHarness',
> 38728)
>
date and time. The following command inserts the date as 1/13/06 when
most systems recognize 38728 as 1/11/06. Why does sql server 2005
increase this value by two days. The code that uses this database is
written in C# and makes heavy use of DateTime especially DateTime.Now,
which thinks 38728 is 1/11/06 so I'm not looking for a different way to
add this record, I want sql server to understand that 38728 is 1/11/06
and not 1/13/06.
Please help!
INSERT INTO [dbo].[Test]
([EID]
,[modifiedBy]
,[modifiedOn])
VALUES
(9999,
'TestHarness',
38728)What result does the following give you :-
select @.@.datefirst
Try SET @.@.DateFirst (two less than the result from above) before running
your insert
HTH. Ryan
"BetaD" <dhorth@.horth.com> wrote in message
news:1136994628.541049.108210@.g44g2000cwa.googlegroups.com...
> I'm trying to update a datetime field in my database with the current
> date and time. The following command inserts the date as 1/13/06 when
> most systems recognize 38728 as 1/11/06. Why does sql server 2005
> increase this value by two days. The code that uses this database is
> written in C# and makes heavy use of DateTime especially DateTime.Now,
> which thinks 38728 is 1/11/06 so I'm not looking for a different way to
> add this record, I want sql server to understand that 38728 is 1/11/06
> and not 1/13/06.
> Please help!
> INSERT INTO [dbo].[Test]
> ([EID]
> ,[modifiedBy]
> ,[modifiedOn])
> VALUES
> (9999,
> 'TestHarness',
> 38728)
>|||You don't use the product correctly. You express datetimes in SQL Server as
a string, not as a
number. Unfortunately, SQL Server accepts a number (implicit datatype conver
sion) and thereby
exposes the internals of the product. And that happens to be different from
some other systems, as
you have noticed. You cannot change the behavior in this regard. Check out:
http://www.karaszi.com/SQLServer/info_datetime.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"BetaD" <dhorth@.horth.com> wrote in message
news:1136994628.541049.108210@.g44g2000cwa.googlegroups.com...
> I'm trying to update a datetime field in my database with the current
> date and time. The following command inserts the date as 1/13/06 when
> most systems recognize 38728 as 1/11/06. Why does sql server 2005
> increase this value by two days. The code that uses this database is
> written in C# and makes heavy use of DateTime especially DateTime.Now,
> which thinks 38728 is 1/11/06 so I'm not looking for a different way to
> add this record, I want sql server to understand that 38728 is 1/11/06
> and not 1/13/06.
> Please help!
> INSERT INTO [dbo].[Test]
> ([EID]
> ,[modifiedBy]
> ,[modifiedOn])
> VALUES
> (9999,
> 'TestHarness',
> 38728)
>|||Why not just take the c# datetime and convert to SqlDateTime and store that?
William Stacey [MVP]
"BetaD" <dhorth@.horth.com> wrote in message
news:1136994628.541049.108210@.g44g2000cwa.googlegroups.com...
> I'm trying to update a datetime field in my database with the current
> date and time. The following command inserts the date as 1/13/06 when
> most systems recognize 38728 as 1/11/06. Why does sql server 2005
> increase this value by two days. The code that uses this database is
> written in C# and makes heavy use of DateTime especially DateTime.Now,
> which thinks 38728 is 1/11/06 so I'm not looking for a different way to
> add this record, I want sql server to understand that 38728 is 1/11/06
> and not 1/13/06.
> Please help!
> INSERT INTO [dbo].[Test]
> ([EID]
> ,[modifiedBy]
> ,[modifiedOn])
> VALUES
> (9999,
> 'TestHarness',
> 38728)
>
DATETIME QUESTION
Is there any command like DateTime.Today (c#) which returns 19.06.2006
00:00:00
it makes zero hour,minutes and second..Savas
SELECT CAST(FLOOR(CAST(GETDATE() AS FLOAT))AS DATETIME)
"Savas Ates" <in da club> wrote in message
news:Ow3oq25kGHA.3936@.TK2MSFTNGP05.phx.gbl...
> Is there any command like DateTime.Today (c#) which returns 19.06.2006
> 00:00:00
> it makes zero hour,minutes and second..
>
>|||The most efficient way I've found is to use the datetime functions.
SELECT DATEADD(DAY, 0, DATEDIFF(DAY, 0, DateTimeColumn))
FROM SomeTable;
"Savas Ates" <in da club> wrote in message
news:Ow3oq25kGHA.3936@.TK2MSFTNGP05.phx.gbl...
> Is there any command like DateTime.Today (c#) which returns 19.06.2006
> 00:00:00
> it makes zero hour,minutes and second..
>
>|||Her's one:
select convert(datetime, convert(varchar(64), getdate(), 112), 112)
ML
http://milambda.blogspot.com/|||I have one morequestion
I have some records
2006-06-17 14:17:28.000
2006-06-17 15:17:28.000
2006-06-17 16:17:28.000
I want to have count number of the records if they have same they .
SELECT COUNT(Referer_IP),Referer_time FROM TBL_REFERER GROUP BY Referer_Time
I wrote that query but it doesnt work ..I think i calculates hour,minute
and second information too.
My problem is that i want to group by them which have same day not caring
about hour ,minute information ?|||One way combining: 2 styles, change getdate() to your column name
SELECT CONVERT(VARCHAR(10),GETDATE(),104) + ' '+
CONVERT(VARCHAR(8),DATEADD(DAY, 0, DATEDIFF(DAY, 0, getdate())) ,108)
or Just this
SELECT CONVERT(VARCHAR(10),GETDATE(),104) + ' 00:00:00'
Denis the SQL Menace
http://sqlservercode.blogspot.com/
Savas Ates wrote:
> Is there any command like DateTime.Today (c#) which returns 19.06.2006
> 00:00:00
> it makes zero hour,minutes and second..|||Well, you need to use one of the calculations provided to you, not
Referer_time.
SELECT
CONVERT(CHAR(10), Referer_Time, 120),
COUNT(*)
FROM
dbo.TBL_REFERER
GROUP BY
CONVERT(CHAR(10), Referer_Time, 120);
"Savas Ates" <in da club> wrote in message
news:%23x%23FdB6kGHA.4816@.TK2MSFTNGP05.phx.gbl...
>I have one morequestion
> I have some records
> 2006-06-17 14:17:28.000
> 2006-06-17 15:17:28.000
> 2006-06-17 16:17:28.000
> I want to have count number of the records if they have same they .
> SELECT COUNT(Referer_IP),Referer_time FROM TBL_REFERER GROUP BY
> Referer_Time
> I wrote that query but it doesnt work ..I think i calculates hour,minute
> and second information too.
> My problem is that i want to group by them which have same day not caring
> about hour ,minute information ?
>
>|||Hi
Take a look at DATEPART system function
"Savas Ates" <in da club> wrote in message
news:%23x%23FdB6kGHA.4816@.TK2MSFTNGP05.phx.gbl...
>I have one morequestion
> I have some records
> 2006-06-17 14:17:28.000
> 2006-06-17 15:17:28.000
> 2006-06-17 16:17:28.000
> I want to have count number of the records if they have same they .
> SELECT COUNT(Referer_IP),Referer_time FROM TBL_REFERER GROUP BY
> Referer_Time
> I wrote that query but it doesnt work ..I think i calculates hour,minute
> and second information too.
> My problem is that i want to group by them which have same day not caring
> about hour ,minute information ?
>
>|||I also need to have WHERE CLAUSE IN my query..
SELECT
CONVERT(CHAR(10), Referer_Time, 120),
COUNT(*)
FROM
dbo.TBL_REFERER
GROUP BY
CONVERT(CHAR(10), Referer_Time, 120) ,Referer_Time
HAVING Referer_Time>DateADd (d,-5,Referer_Time)
I wrote That it returns wrong results...|||>I also need to have WHERE CLAUSE IN my query..
Can you give us a full spec/requirement instead of adding individual things
you "need"? I feel like E.T. following a trail of reese's pieces.
> SELECT
> CONVERT(CHAR(10), Referer_Time, 120),
> COUNT(*)
> FROM
> dbo.TBL_REFERER
> GROUP BY
> CONVERT(CHAR(10), Referer_Time, 120) ,Referer_Time
> HAVING Referer_Time>DateADd (d,-5,Referer_Time)
> I wrote That it returns wrong results...
What are "wrong results"? Can you show DDL, sample data, and desired
output? Please follow guidelines in http://www.aspfaq.com/5006
00:00:00
it makes zero hour,minutes and second..Savas
SELECT CAST(FLOOR(CAST(GETDATE() AS FLOAT))AS DATETIME)
"Savas Ates" <in da club> wrote in message
news:Ow3oq25kGHA.3936@.TK2MSFTNGP05.phx.gbl...
> Is there any command like DateTime.Today (c#) which returns 19.06.2006
> 00:00:00
> it makes zero hour,minutes and second..
>
>|||The most efficient way I've found is to use the datetime functions.
SELECT DATEADD(DAY, 0, DATEDIFF(DAY, 0, DateTimeColumn))
FROM SomeTable;
"Savas Ates" <in da club> wrote in message
news:Ow3oq25kGHA.3936@.TK2MSFTNGP05.phx.gbl...
> Is there any command like DateTime.Today (c#) which returns 19.06.2006
> 00:00:00
> it makes zero hour,minutes and second..
>
>|||Her's one:
select convert(datetime, convert(varchar(64), getdate(), 112), 112)
ML
http://milambda.blogspot.com/|||I have one morequestion
I have some records
2006-06-17 14:17:28.000
2006-06-17 15:17:28.000
2006-06-17 16:17:28.000
I want to have count number of the records if they have same they .
SELECT COUNT(Referer_IP),Referer_time FROM TBL_REFERER GROUP BY Referer_Time
I wrote that query but it doesnt work ..I think i calculates hour,minute
and second information too.
My problem is that i want to group by them which have same day not caring
about hour ,minute information ?|||One way combining: 2 styles, change getdate() to your column name
SELECT CONVERT(VARCHAR(10),GETDATE(),104) + ' '+
CONVERT(VARCHAR(8),DATEADD(DAY, 0, DATEDIFF(DAY, 0, getdate())) ,108)
or Just this
SELECT CONVERT(VARCHAR(10),GETDATE(),104) + ' 00:00:00'
Denis the SQL Menace
http://sqlservercode.blogspot.com/
Savas Ates wrote:
> Is there any command like DateTime.Today (c#) which returns 19.06.2006
> 00:00:00
> it makes zero hour,minutes and second..|||Well, you need to use one of the calculations provided to you, not
Referer_time.
SELECT
CONVERT(CHAR(10), Referer_Time, 120),
COUNT(*)
FROM
dbo.TBL_REFERER
GROUP BY
CONVERT(CHAR(10), Referer_Time, 120);
"Savas Ates" <in da club> wrote in message
news:%23x%23FdB6kGHA.4816@.TK2MSFTNGP05.phx.gbl...
>I have one morequestion
> I have some records
> 2006-06-17 14:17:28.000
> 2006-06-17 15:17:28.000
> 2006-06-17 16:17:28.000
> I want to have count number of the records if they have same they .
> SELECT COUNT(Referer_IP),Referer_time FROM TBL_REFERER GROUP BY
> Referer_Time
> I wrote that query but it doesnt work ..I think i calculates hour,minute
> and second information too.
> My problem is that i want to group by them which have same day not caring
> about hour ,minute information ?
>
>|||Hi
Take a look at DATEPART system function
"Savas Ates" <in da club> wrote in message
news:%23x%23FdB6kGHA.4816@.TK2MSFTNGP05.phx.gbl...
>I have one morequestion
> I have some records
> 2006-06-17 14:17:28.000
> 2006-06-17 15:17:28.000
> 2006-06-17 16:17:28.000
> I want to have count number of the records if they have same they .
> SELECT COUNT(Referer_IP),Referer_time FROM TBL_REFERER GROUP BY
> Referer_Time
> I wrote that query but it doesnt work ..I think i calculates hour,minute
> and second information too.
> My problem is that i want to group by them which have same day not caring
> about hour ,minute information ?
>
>|||I also need to have WHERE CLAUSE IN my query..
SELECT
CONVERT(CHAR(10), Referer_Time, 120),
COUNT(*)
FROM
dbo.TBL_REFERER
GROUP BY
CONVERT(CHAR(10), Referer_Time, 120) ,Referer_Time
HAVING Referer_Time>DateADd (d,-5,Referer_Time)
I wrote That it returns wrong results...|||>I also need to have WHERE CLAUSE IN my query..
Can you give us a full spec/requirement instead of adding individual things
you "need"? I feel like E.T. following a trail of reese's pieces.
> SELECT
> CONVERT(CHAR(10), Referer_Time, 120),
> COUNT(*)
> FROM
> dbo.TBL_REFERER
> GROUP BY
> CONVERT(CHAR(10), Referer_Time, 120) ,Referer_Time
> HAVING Referer_Time>DateADd (d,-5,Referer_Time)
> I wrote That it returns wrong results...
What are "wrong results"? Can you show DDL, sample data, and desired
output? Please follow guidelines in http://www.aspfaq.com/5006
DateTime question
I'm trying to update a datetime field in my database with the current
date and time. The following command inserts the date as 1/13/06 when
most systems recognize 38728 as 1/11/06. Why does sql server 2005
increase this value by two days. The code that uses this database is
written in C# and makes heavy use of DateTime especially DateTime.Now,
which thinks 38728 is 1/11/06 so I'm not looking for a different way to
add this record, I want sql server to understand that 38728 is 1/11/06
and not 1/13/06.
Please help!
INSERT INTO [dbo].[Test]
([EID]
,[modifiedBy]
,[modifiedOn])
VALUES
(9999,
'TestHarness',
38728)
What result does the following give you :-
select @.@.datefirst
Try SET @.@.DateFirst (two less than the result from above) before running
your insert
HTH. Ryan
"BetaD" <dhorth@.horth.com> wrote in message
news:1136994628.541049.108210@.g44g2000cwa.googlegr oups.com...
> I'm trying to update a datetime field in my database with the current
> date and time. The following command inserts the date as 1/13/06 when
> most systems recognize 38728 as 1/11/06. Why does sql server 2005
> increase this value by two days. The code that uses this database is
> written in C# and makes heavy use of DateTime especially DateTime.Now,
> which thinks 38728 is 1/11/06 so I'm not looking for a different way to
> add this record, I want sql server to understand that 38728 is 1/11/06
> and not 1/13/06.
> Please help!
> INSERT INTO [dbo].[Test]
> ([EID]
> ,[modifiedBy]
> ,[modifiedOn])
> VALUES
> (9999,
> 'TestHarness',
> 38728)
>
|||You don't use the product correctly. You express datetimes in SQL Server as a string, not as a
number. Unfortunately, SQL Server accepts a number (implicit datatype conversion) and thereby
exposes the internals of the product. And that happens to be different from some other systems, as
you have noticed. You cannot change the behavior in this regard. Check out:
http://www.karaszi.com/SQLServer/info_datetime.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"BetaD" <dhorth@.horth.com> wrote in message
news:1136994628.541049.108210@.g44g2000cwa.googlegr oups.com...
> I'm trying to update a datetime field in my database with the current
> date and time. The following command inserts the date as 1/13/06 when
> most systems recognize 38728 as 1/11/06. Why does sql server 2005
> increase this value by two days. The code that uses this database is
> written in C# and makes heavy use of DateTime especially DateTime.Now,
> which thinks 38728 is 1/11/06 so I'm not looking for a different way to
> add this record, I want sql server to understand that 38728 is 1/11/06
> and not 1/13/06.
> Please help!
> INSERT INTO [dbo].[Test]
> ([EID]
> ,[modifiedBy]
> ,[modifiedOn])
> VALUES
> (9999,
> 'TestHarness',
> 38728)
>
|||Why not just take the c# datetime and convert to SqlDateTime and store that?
William Stacey [MVP]
"BetaD" <dhorth@.horth.com> wrote in message
news:1136994628.541049.108210@.g44g2000cwa.googlegr oups.com...
> I'm trying to update a datetime field in my database with the current
> date and time. The following command inserts the date as 1/13/06 when
> most systems recognize 38728 as 1/11/06. Why does sql server 2005
> increase this value by two days. The code that uses this database is
> written in C# and makes heavy use of DateTime especially DateTime.Now,
> which thinks 38728 is 1/11/06 so I'm not looking for a different way to
> add this record, I want sql server to understand that 38728 is 1/11/06
> and not 1/13/06.
> Please help!
> INSERT INTO [dbo].[Test]
> ([EID]
> ,[modifiedBy]
> ,[modifiedOn])
> VALUES
> (9999,
> 'TestHarness',
> 38728)
>
sql
date and time. The following command inserts the date as 1/13/06 when
most systems recognize 38728 as 1/11/06. Why does sql server 2005
increase this value by two days. The code that uses this database is
written in C# and makes heavy use of DateTime especially DateTime.Now,
which thinks 38728 is 1/11/06 so I'm not looking for a different way to
add this record, I want sql server to understand that 38728 is 1/11/06
and not 1/13/06.
Please help!
INSERT INTO [dbo].[Test]
([EID]
,[modifiedBy]
,[modifiedOn])
VALUES
(9999,
'TestHarness',
38728)
What result does the following give you :-
select @.@.datefirst
Try SET @.@.DateFirst (two less than the result from above) before running
your insert
HTH. Ryan
"BetaD" <dhorth@.horth.com> wrote in message
news:1136994628.541049.108210@.g44g2000cwa.googlegr oups.com...
> I'm trying to update a datetime field in my database with the current
> date and time. The following command inserts the date as 1/13/06 when
> most systems recognize 38728 as 1/11/06. Why does sql server 2005
> increase this value by two days. The code that uses this database is
> written in C# and makes heavy use of DateTime especially DateTime.Now,
> which thinks 38728 is 1/11/06 so I'm not looking for a different way to
> add this record, I want sql server to understand that 38728 is 1/11/06
> and not 1/13/06.
> Please help!
> INSERT INTO [dbo].[Test]
> ([EID]
> ,[modifiedBy]
> ,[modifiedOn])
> VALUES
> (9999,
> 'TestHarness',
> 38728)
>
|||You don't use the product correctly. You express datetimes in SQL Server as a string, not as a
number. Unfortunately, SQL Server accepts a number (implicit datatype conversion) and thereby
exposes the internals of the product. And that happens to be different from some other systems, as
you have noticed. You cannot change the behavior in this regard. Check out:
http://www.karaszi.com/SQLServer/info_datetime.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"BetaD" <dhorth@.horth.com> wrote in message
news:1136994628.541049.108210@.g44g2000cwa.googlegr oups.com...
> I'm trying to update a datetime field in my database with the current
> date and time. The following command inserts the date as 1/13/06 when
> most systems recognize 38728 as 1/11/06. Why does sql server 2005
> increase this value by two days. The code that uses this database is
> written in C# and makes heavy use of DateTime especially DateTime.Now,
> which thinks 38728 is 1/11/06 so I'm not looking for a different way to
> add this record, I want sql server to understand that 38728 is 1/11/06
> and not 1/13/06.
> Please help!
> INSERT INTO [dbo].[Test]
> ([EID]
> ,[modifiedBy]
> ,[modifiedOn])
> VALUES
> (9999,
> 'TestHarness',
> 38728)
>
|||Why not just take the c# datetime and convert to SqlDateTime and store that?
William Stacey [MVP]
"BetaD" <dhorth@.horth.com> wrote in message
news:1136994628.541049.108210@.g44g2000cwa.googlegr oups.com...
> I'm trying to update a datetime field in my database with the current
> date and time. The following command inserts the date as 1/13/06 when
> most systems recognize 38728 as 1/11/06. Why does sql server 2005
> increase this value by two days. The code that uses this database is
> written in C# and makes heavy use of DateTime especially DateTime.Now,
> which thinks 38728 is 1/11/06 so I'm not looking for a different way to
> add this record, I want sql server to understand that 38728 is 1/11/06
> and not 1/13/06.
> Please help!
> INSERT INTO [dbo].[Test]
> ([EID]
> ,[modifiedBy]
> ,[modifiedOn])
> VALUES
> (9999,
> 'TestHarness',
> 38728)
>
sql
Saturday, February 25, 2012
Dates in a SQL Command
hi,
i have to return all the dates in a date interval, but they are not stored in a table. what i need is to calculate all the dates between 2 given dates.
is it possible to do this in a SQL command ?
thanks,
levogiro.
levogiro:
One way to do this is to loop through a table of numbers. Here the table "small_iterator" is simply a table of integers 1-32767:
declare @.dayDiff integer
declare @.firstDate datetime set @.firstDate = '12/1/6'
declare @.secondDate datetime set @.secondDate = '1/3/7'select @.dayDiff = datediff (day, @.firstDate, @.secondDate)
--select @.dayDiff as [@.dayDiff]select convert (varchar (10), @.firstDate + iter - 1, 101) as rangeDate
from small_iterator (nolock)
where iter <= @.dayDiff + 1-- Sample Output:
-- rangeDate
-- -
-- 12/01/2006
-- 12/02/2006
-- ...
-- 01/03/2007
Another solution is to use a calendar table as described in this article:
http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-calendar-table.html
This article describes the use of a "Numbers" table:
http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-numbers-table.html
I hope this helps.
Dave
Friday, February 17, 2012
DateDiff function problem
Hi Folks,
Looking for assistance on using the datediff command to convert a date\time
stamp into unix time. A few of you where a great help the last time
directing me in the right direction on doing this. My problem is that the
time stamp is one hour out (one hour ahead) . The below syntax is how
capturing the date and time stamp and converting. I have check my clocking
setting on my server and anything seems fine. I working with British\Irsh
time zone. Anyone any idea where I'm going wrong?
SELECT @.TransactDateCal = (SELECT DATEDIFF(s, '19700101', GetDate ()))
Many thanks,
Liam.Hi
The 1 hour could be due to Summer Time / Daylight Savings Time.
What if you change your server to be exact GMT?
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Liam Mac" wrote:
> Hi Folks,
> Looking for assistance on using the datediff command to convert a date\tim
e
> stamp into unix time. A few of you where a great help the last time
> directing me in the right direction on doing this. My problem is that the
> time stamp is one hour out (one hour ahead) . The below syntax is how
> capturing the date and time stamp and converting. I have check my clocking
> setting on my server and anything seems fine. I working with British\Irsh
> time zone. Anyone any idea where I'm going wrong?
> SELECT @.TransactDateCal = (SELECT DATEDIFF(s, '19700101', GetDate ()))
> Many thanks,
> Liam.
>|||Well, do you observe daylight savings time? Is your machine set up to do
so? Does SELECT GETDATE() yield the right date/time?
"Liam Mac" <LiamMac@.discussions.microsoft.com> wrote in message
news:B197644A-1531-4CB6-A394-69F47FAD74C2@.microsoft.com...
> Hi Folks,
> Looking for assistance on using the datediff command to convert a
> date\time
> stamp into unix time. A few of you where a great help the last time
> directing me in the right direction on doing this. My problem is that the
> time stamp is one hour out (one hour ahead) . The below syntax is how
> capturing the date and time stamp and converting. I have check my clocking
> setting on my server and anything seems fine. I working with British\Irsh
> time zone. Anyone any idea where I'm going wrong?
> SELECT @.TransactDateCal = (SELECT DATEDIFF(s, '19700101', GetDate ()))
> Many thanks,
> Liam.
>|||Or just use GETUTCDATE() instead?
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:7CBC1E33-8E30-4CCF-8F96-BD620B31182E@.microsoft.com...
> Hi
> The 1 hour could be due to Summer Time / Daylight Savings Time.
> What if you change your server to be exact GMT?
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
> "Liam Mac" wrote:
>
date\time
the
clocking
British\Irsh|||thanks folks, below sytnax worked.
"Adam Machanic" wrote:
> Or just use GETUTCDATE() instead?
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.datamanipulation.net
> --
>
> "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
> news:7CBC1E33-8E30-4CCF-8F96-BD620B31182E@.microsoft.com...
> date\time
> the
> clocking
> British\Irsh
>
>
Looking for assistance on using the datediff command to convert a date\time
stamp into unix time. A few of you where a great help the last time
directing me in the right direction on doing this. My problem is that the
time stamp is one hour out (one hour ahead) . The below syntax is how
capturing the date and time stamp and converting. I have check my clocking
setting on my server and anything seems fine. I working with British\Irsh
time zone. Anyone any idea where I'm going wrong?
SELECT @.TransactDateCal = (SELECT DATEDIFF(s, '19700101', GetDate ()))
Many thanks,
Liam.Hi
The 1 hour could be due to Summer Time / Daylight Savings Time.
What if you change your server to be exact GMT?
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Liam Mac" wrote:
> Hi Folks,
> Looking for assistance on using the datediff command to convert a date\tim
e
> stamp into unix time. A few of you where a great help the last time
> directing me in the right direction on doing this. My problem is that the
> time stamp is one hour out (one hour ahead) . The below syntax is how
> capturing the date and time stamp and converting. I have check my clocking
> setting on my server and anything seems fine. I working with British\Irsh
> time zone. Anyone any idea where I'm going wrong?
> SELECT @.TransactDateCal = (SELECT DATEDIFF(s, '19700101', GetDate ()))
> Many thanks,
> Liam.
>|||Well, do you observe daylight savings time? Is your machine set up to do
so? Does SELECT GETDATE() yield the right date/time?
"Liam Mac" <LiamMac@.discussions.microsoft.com> wrote in message
news:B197644A-1531-4CB6-A394-69F47FAD74C2@.microsoft.com...
> Hi Folks,
> Looking for assistance on using the datediff command to convert a
> date\time
> stamp into unix time. A few of you where a great help the last time
> directing me in the right direction on doing this. My problem is that the
> time stamp is one hour out (one hour ahead) . The below syntax is how
> capturing the date and time stamp and converting. I have check my clocking
> setting on my server and anything seems fine. I working with British\Irsh
> time zone. Anyone any idea where I'm going wrong?
> SELECT @.TransactDateCal = (SELECT DATEDIFF(s, '19700101', GetDate ()))
> Many thanks,
> Liam.
>|||Or just use GETUTCDATE() instead?
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:7CBC1E33-8E30-4CCF-8F96-BD620B31182E@.microsoft.com...
> Hi
> The 1 hour could be due to Summer Time / Daylight Savings Time.
> What if you change your server to be exact GMT?
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
> "Liam Mac" wrote:
>
date\time
the
clocking
British\Irsh|||thanks folks, below sytnax worked.
"Adam Machanic" wrote:
> Or just use GETUTCDATE() instead?
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.datamanipulation.net
> --
>
> "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
> news:7CBC1E33-8E30-4CCF-8F96-BD620B31182E@.microsoft.com...
> date\time
> the
> clocking
> British\Irsh
>
>
Subscribe to:
Posts (Atom)