• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3659
  • Last Modified:

SQL- Start of month date function

i am currently using this to get the start of the day,
CAST(FLOOR(CAST(GETDATE() AS FLOAT))AS DATETIME)

what would i use to get the start of the month, also could you explain what the expression means, thank you
0
meteorelec
Asked:
meteorelec
  • 3
  • 2
1 Solution
 
contactkarthiCommented:
how about this one
 
CREATE FUNCTION [dbo].[ufn_GetFirstDayOfMonth] ( @pInputDate    DATETIME )
RETURNS DATETIME
BEGIN 
    RETURN CAST(CAST(YEAR(@pInputDate) AS VARCHAR(4)) + '/' + 
                CAST(MONTH(@pInputDate) AS VARCHAR(2)) + '/01' AS DATETIME) 
END
GO

Open in new window

0
 
Patrick MatthewsCommented:
Hello meteorelec,

To get today, I like this better:

CONVERT(datetime, CONVERT(varchar, GETDATE(), 101))

1st of month:

CONVERT(datetime, CONVERT(varchar, DATEADD(d, 1 - DAY(GETDATE())), 101)

Regards,

Patrick
0
 
meteorelecAuthor Commented:
Hi Patrick,

CONVERT(datetime, CONVERT(varchar, DATEADD(d, 1 - DAY(GETDATE())), 101)

is give me back

The dateadd function requires 3 argument(s)
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Patrick MatthewsCommented:
Sorry!

CONVERT(datetime, CONVERT(varchar, DATEADD(d, 1 - DAY(GETDATE()), GETDATE()), 101)
0
 
meteorelecAuthor Commented:
Incorrect syntax near ')'.

is what i'm getting now
0
 
Patrick MatthewsCommented:
CONVERT(datetime, CONVERT(varchar, DATEADD(d, 1 - DAY(GETDATE()), GETDATE()), 102))
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now